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.3k
5 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.3k
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.3k
2 votes
Accepted

Want to use PostgreSQL db and ST_AsMVT() to develop a tile server, assuming my start point is a GeoTIFF

So with the help of @geozelot i was able to conclude the following: I cannot use vector tiles for my geoTIFF as it is raster data To accomplish my initial goal generating a tile server for raster ...
user avatar
  • 51
2 votes

PostGIS performance using ST_Subdivide

Hello @marieneighe If I were in your shoes, my question is only; To Subdivide or Not To Subdivide As a matter of routine I refer to the below when a new dataset comes in Do a test on the size of the ...
user avatar
  • 427
1 vote

Showing Origins on ARRAY multiple routes - pgRouting

Let's say you want to compute a route from A to B then your start and destination vertex will be in the output as start_vid and end_vid. For the sake of simplicity this query is using some arbitrary ...
user avatar
1 vote

Grouping polygons by similarity keeping records of ids

Depending on the actual similarity (vertex distribution in particular), you could probably get away with groups over gridded centroids, e.g. SELECT ARRAY_AGG(id) AS ids, ST_Union(geom) AS geom ...
user avatar
  • 23.3k
1 vote

Use PostGIS to find all the most outer points

Thanks for some good comments I made it work using. SELECT ST_ASGEOJSON((ST_DumpPoints(ST_ConvexHull(st_collect(geog::geometry)))).geom) FROM sources;
user avatar
1 vote
Accepted

PostGIS function that updates WKT geometries to geometry type with st_geomfromtext

Before the update: Run SELECT pa.attrelid::REGCLASS, pa.attname FROM pg_attribute AS pa WHERE pa.attrelid = '<schema>.<table>'::REGCLASS AND pa.attnum > 0 AND pa.attname ...
user avatar
  • 23.3k

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