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.