Friday, 10 January 2020

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


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

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...