[GIS] Joining 2 tables to create layer in Geoserver with MultiPolygon and Point data types

geoserverpostgispostgresql

I got problem when using the SQL View function. My objective is to create layer in geoserver from joining 2 tables.

I have 2 tables, table_a:

                                 Table "public.table_a"
         Column          |             Type             |        Modifiers
-------------------------+------------------------------+------------------------
 id                      | integer                      | not null default 
 name                    | character varying(30)        | not null
 remarks                 | text                         |
 boundary                | geography(MultiPolygon,4326) |

And table_b:

                           Table "public.table_b"
         Column          |         Type          |           Modifiers
-------------------------+-----------------------+-------------------------------
 id                      | integer               | not null default
 sequence                | integer               | not null
 point                   | geography(Point,4326) |
 table_a_id              | integer               | not null

Here is the query for the SQL View:

SELECT
    table_a.name, table_a.remarks, table_a.boundary,
    table_b.sequence, table_b.point, table_b.table_a_id
FROM
    table_a
        INNER JOIN table_b ON (table_a.id = table_b.table_a_id)

And the result:

      Name         | Type
-------------------+-----------
table_a.name       | String
table_a.remarks    | String
table_b.sequence   | Integer
table_b.table_a_id | Integer

The problem with the result is the table_a.boundary column and table_b.point column cannot be display.

The result is different when I tried with Publish function for each table:
Publish

Publish table_a:

      Name         | Type
-------------------+-----------
name               | String
remarks            | String
boundary           | MultiPolygon

Publish table_b:

  Name             | Type
-------------------+-----------
sequence           | Integer
Point              | Point
table_a_id         | Integer

My Question is:

  1. What is the query to create the MultiPolygon data type in GeoServer SQL View from geography(MultiPolygon) in postgres data type?
  2. What is the query to create the Point data type in GeoServer SQL View from geography(Point) in postgres data type?

I'm using:

  1. PostgreSQL 8.4
  2. Postgis-1.5.3 and I find that there is 2 types of geography in there:
    1. '$libdir/postgis-1.5', 'geography_from_geometry' (from function geography(geometry))
    2. '$libdir/postgis-1.5', 'geography_enforce_typmod' (from function geography(geography, integer, boolean))
  3. GeoServer-2.1.3

Best Answer

You can cast geography types to geometry:

SELECT
  name, remarks, boundary::geometry,
  sequence, point::geometry, table_a_id
FROM table_a
INNER JOIN table_b ON table_a.id = table_a_id
Related Question