Hot answers tagged

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 ...
user avatar
  • 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)...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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' ...
user avatar
  • 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 ...
user avatar
  • 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 ( ...
user avatar
  • 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)
user avatar

Only top scored, non community-wiki answers of a minimum length are eligible