Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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
share|improve this question
add comment

4 Answers

up vote 2 down vote accepted
+100

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
share|improve this answer
    
Thank you very much, this query works. First time I see the :: sign. –  Crazenezz Jun 19 '12 at 1:57
add comment

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.

share|improve this answer
    
What I mean is there is no problem when I try to query from postgre. The problem occur when I try query in SQL View on GeoServer, the column with Geography data types cannot be shown. But when I try with the publish function (create layer automatically based on postgis table) the Geography data types automatically recognize by GeoServer and translate to MultiPolygon and Point data types. Are you familiar with GeoServer? –  Crazenezz Jun 14 '12 at 15:37
1  
No, I don't work with Geoserver, so I'll defer to others with more knowledge. But I wouldn't be surprised if the problem arises because your creating a view in SQL view with two different geometry columns... –  Micha Jun 15 '12 at 14:02
    
I try with single table, but the problem still occur. Thanks for the suggestion. –  Crazenezz Jun 16 '12 at 6:14
    
@Crazenezz the problem you're describing also happens if GeoServer publishes an Oracle view that isn't registered in user_sdo_geom_metadata (Oracle's equivalent of PostGIS geometry_columns). If you have added your view in PostGIS (not via GeoServer) and included it in geometry_columns but it doesn't work try restarting GeoServer to reload all layers. –  tomfumb Jun 19 '12 at 1:08
add comment

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:

enter image description here

share|improve this answer
add comment

This is applicable to SQL Server too. You can join two tables in SQL View and it is possible that the other table has no geography/geometry data.

share|improve this answer
    
Welcome to GIS SE! Our protocols can take a little getting used to but you have asked a question in the area that is reserved for a direct Answer to the Question asked. If you had been around for a while then it would have been suitable as a comment. Alternatively, you could expand upon it and ask it as a question to start building some reputation here to unlock privileges like being able to comment on any post. –  PolyGeo Oct 9 '13 at 5:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.