11
votes
Accepted
Deleting small holes in polygons with PostGIS
This is one of those processes which are simple in an iterative language, but trickier in set-oriented SQL. But the ST_DumpX set-returning functions in PostGIS and the array handling in Postgres make ...
- 3,601
6
votes
Accepted
How to make column in view to have correct srid of origin table
You need to properly cast the column type to include the respective type modifiers like so:
CREATE OR REPLACE VIEW myview AS
SELECT
a.id,
ST_SetSRID(dmp.geom, 25832)::GEOMETRY(POLYGON, 25832)...
- 23.2k
5
votes
Deleting small holes in polygons with PostGIS
Just to add a little less convoluted solution in SQL, shifting construction logic to ST_BuildArea - the LATERAL join predicate is an elegant method to expand and iterate over sets of rows returned ...
- 23.2k
4
votes
Accepted
st_dump - why is the "path" an array?
It is possible to have nested collections, and in this case the array makes sense:
select path, st_asText(geom)
from st_dump('GEOMETRYCOLLECTION(MULTILINESTRING((1 2,3 4),(3 4,4 5)),MULTIPOINT(1 2,3 ...
- 34.3k
4
votes
Accepted
Adding a polygon to a multipolygon column
I strongly recommend using a trigger to work this out - apart from some other things they have less confusing side effects, and can be extended much easier:
CREATE FUNCTION ...
- 23.2k
3
votes
Accepted
Get geohash neighbors with PostGIS
I had have to do this a while ago, but I had to recreate from the top of my head here; this should be correct for all but the top-level 1-digit hashes¹:
WITH
geo AS (
SELECT
'dqcjqc' ...
- 23.2k
3
votes
How to get a list of lines, with a list of polygons that they intersect?
This is generally solved using an [INNER] JOIN, or if you are also interested in lines that do not intersect any grid cells in the result set, a LEFT JOIN; you then want to GROUP BY your line_ids and ...
- 23.2k
3
votes
Deleting small holes in polygons with PostGIS
Based on your example, just extract the interior ring of your geom, calculate it's area and use it as condition in where clause as:
SELECT gid, ST_Collect(ST_MakePolygon(geom)) As geom
FROM (
...
- 423
1
vote
Accepted
Why is h3.polyfill(geojson, 15) returning empty list for this polygon?
You need to use h3.polyfill_geojson(geojson, 15) or h3.polyfill(geojson, 15, geo_json_conformant=True)
- 7,763
Only top scored, non community-wiki answers of a minimum length are eligible