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 ...
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)...
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 ...
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 ...
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 ...
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' ...
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 ...
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 (
...
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)
Only top scored, non community-wiki answers of a minimum length are eligible
Related Tags
postgis × 8611postgresql × 2753
qgis × 1307
geoserver × 501
sql × 501
pgrouting × 429
raster × 380
openstreetmap × 364
python × 311
polygon × 288
coordinate-system × 278
geometry × 255
shapefile × 244
postgis-2.0 × 196
intersection × 183
gdal × 179
ogr2ogr × 174
openlayers-2 × 169
geojson × 166
linestring × 155
point × 152
query × 151
distance × 137
osm2pgsql × 134
srid × 121