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
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, , 'MULTIPOLYGON')
No comments:
Post a Comment