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.

When I want to store the results of a spatial query as another PostGIS table, what should I do? I am interested in identifying all residential buildings that fall within a set of parcels:

CREATE TABLE residentialBuildings AS
SELECT buildings.the_geom
FROM parcels as p
JOIN buildings as b
ON ST_Contains(p.the_geom, b.the_geom)
WHERE p.residentialarea > 0;

This seems to work, but it does not return a spatial result - instead the table has the binary representation of the geometry. I'm not sure what I should be doing to create a table as a result. Do I need to first make a table, and then convert the result into a geometry?

(I have read the documentation, but I feel like I'm missing the right search terms as I feel this must be a fairly obvious thing to do).

I realize that there are many commands to make a geometry

ST_GeogFromText 
ST_GeographyFromText 
ST_GeogFromWKB 
ST_GeomCollFromText 
ST_GeomFromEWKB 
ST_GeomFromEWKT 
ST_GeometryFromText 
ST_GeomFromGML 
ST_GeomFromKML 
ST_GMLToSQL 
ST_GeomFromText 
ST_GeomFromWKB 

but I don't know which option to choose from, or if this is the correct approach.

share|improve this question
add comment

2 Answers

up vote 6 down vote accepted

You can manually register Geometry Columns in geometry_columns. Something like:

INSERT INTO geometry_columns (f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
VALUES ('', 'public', 'residentialBuildings', 'the_geom', 2, <yourSRID>, 'MULTIPOLYGON')
share|improve this answer
    
I'm getting an error message that says ERROR: column "polygon" does not exist SQL state: 42703 Do you know what might be causing the error? The table does exist, and I also tried using POLYGON. –  djq Nov 18 '11 at 22:56
    
Sorry, forgot to add ''. –  underdark Nov 19 '11 at 12:49
    
Thanks @underdark - worked perfectly! –  djq Nov 19 '11 at 16:30
add comment

The result is a spatial table with only one column in geometry form. The binary representation is correct. If you are using this outside of PostGIS (say ArcGIS or QGIS or whatever), it is probably looking for two things:

share|improve this answer
    
To clarify (I will update the question) I want to create a table in PostGIS with the result. –  djq Nov 18 '11 at 18:55
    
yeah, so your approach and result are OK. Just like in GeoDatabases, the rows in the geometry column are made of serialized geometries. If you want to see the contents, just use ST_AsText on the geometry column, just like you would do with any other postgis table. –  Ragi Yaser Burhum Nov 18 '11 at 19:22
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.