PostGIS¶
#TODO¶
Personal Experience¶
N/A
Tools & Software Integrations¶
N/A
Recommended Plugins¶
N/A
Resources¶
N/A
Notes and Troubleshooting¶
Convert Multipoint to Point¶
To convert "Multipoint" to "Point", you have to use ST_Dump:
SELECT (ST_Dump(the_geom)).geom AS the_POINT_geom
FROM MULTIPOINT_table;
Source: https://gis.stackexchange.com/questions/86268/multipoint-vs-point-postgis
Select X and Y coordinates from Point Geometries¶
SELECT ST_X(geom), ST_Y(geom)
FROM POINT_table;
Display Geometry as WKT¶
SELECT ST_AsText(geom)
FROM Geometry_table;
Aggregate attributes from nested polygons at various spatial scales with PostGIS¶
To create a lookup table of names and IDs related to nested polygons at multiple scales you can use CROSS JOIN LATERAL. So, for a table with an entry for every LSOA in the UK that includes the names and IDs of the MSOAs and Districts that contain them use the following:
SELECT lsoa.lsoa_11nm,
lsoa.lsoa_11cd,
msoa.msoa_11nm,
msoa.msoa_11cd,
district.name,
district.census_code
FROM (
SELECT geom
FROM bounds.country_region
WHERE fid = 978
) AS wales
CROSS JOIN LATERAL (
SELECT lsoa_11nm, lsoa_11cd, geom
FROM bounds.census_lsoa_gen_clip_2011
WHERE ST_Intersects(wales.geom, geom) AND
ST_Contains(wales.geom, ST_PointOnSurface(geom))
) AS lsoa
CROSS JOIN LATERAL (
SELECT msoa_11nm, msoa_11cd
FROM bounds.census_msoa_gen_clip_2011
WHERE ST_Intersects(lsoa.geom, geom) AND
ST_Contains(geom, ST_PointOnSurface(lsoa.geom))
) as msoa
CROSS JOIN LATERAL (
SELECT name, census_code
FROM bounds.district_borough_unitary AS d
WHERE ST_Intersects(lsoa.geom, geom) AND
ST_Contains(geom, ST_PointOnSurface(lsoa.geom))
) as district;
NOTE: Using ST_Contains instead of ST_Within for evaluating the spatial join is fine but it is best to combine this with ST_PointOnSurface to ensure that the query is evaluating based on a point that actually falls within the polygon being tested. If the polygon has an irregular or even horseshoe shape the point being evaluated by ST_Contains may fall outside polygon in another area it encircles. Performing a prior check with ST_Intersects helps optimise the query.