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


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


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')
  • 1
    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

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:

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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