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

Question

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 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

Answer 1

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

Answer 2

I suspect that the way you're creating the view, there's no row in the PostGIS geometry_columns table, and that's why Geoserver doesn't recognize it. If you want to try straight in PostGIS then:

CREATE VIEW a_b AS 
SELECT a.name, a.remarks, b.sequence, b.table_a_id 
        a.boundary AS poly_geom, b.point as point_geom
FROM table_a AS a, table_b AS b
WHERE a.id = b.table_a_id


INSERT INTO geometry_columns(
         f_table_name,  f_geometry_column, coord_dimension, srid, "type")
SELECT 'a_b', 'poly_geom', ST_CoordDim(boundary), ST_SRID(boundary), 
         GeometryType(tboundary)
FROM table_a LIMIT 1; 


INSERT INTO geometry_columns(
         f_table_name,  f_geometry_column, coord_dimension, srid, "type")
SELECT 'a_b', 'point_geom', ST_CoordDim(boundary), ST_SRID(boundary), 
         GeometryType(tboundary)
FROM table_b LIMIT 1;

That might do it.

Answer 3

Well you could try to make a third table with the geometry type GeometryCollection. And insert the content of both tables. I use it to render a layer of Points and MultiLineStrings. This is how it looks like at the geoserver panel: