Creating a spatial PostGIS table as a result of a PostGIS query

Question

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.

Answer 1

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

Answer 2

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:

• An entry in the geometry_columns. Solve it by adding an entry manually.
• A unique id. Solve it, by creating a sequence and make it the unique id.