New answers tagged

0 votes

Save xml CityModel files to PostGIS

CityGML Data such as Level-of-Detail 1 can be uploaded with the 3D City Database Importer / Exporter Tool downloadable here: 3D City Database. The setup is relatively straightforward and documented ...
user avatar
  • 151
3 votes
Accepted

pgRouting computation time & code efficiency

Instead of calling pgr_dijkstra multiple times you should be invoking it with 2 arrays for sources and targets and not single values (thank you @DavidP for highlighting this). This way the graph will ...
user avatar
1 vote

Creating a grid which takes coordinates as an input

So, if I understand the question correctly, there are many ways to solve your question, and this is one of them. Proceed as follows: Replace the values of the northwest corner coordinate 100,100 with ...
user avatar
2 votes

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
2 votes

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
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
  • 63
0 votes

"libSFCGAL.so.1: undefined symbol" upgrading to Postgis 2.2.2 (and 2.2.1)

In case anybody faces below error ERROR: could not load library "/usr/pgsql-12/lib/postgis-3.so": /lib64/libSFCGAL.so.1: undefined symbol: ...
user avatar
0 votes

Buffer with PostGIS in the form of a pentagon or hexagon

Based on dr_jts response, I propose the following version of the design and name of the new spatial SQL function: This simple spatial SQL function takes as input both one geospatial point and many ...
user avatar
3 votes

Deleting small holes in polygons with PostGIS

Based on dr_jts response, I propose the following version of the design and name of the new spatial SQL function: Create a spatial custom SQL function: CREATE OR REPLACE FUNCTION ...
user avatar
7 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.3k
0 votes

ERROR: function st_straightskeleton(geometry) does not exist

You need the extension postgis_sfcgal ex) CREATE EXTENSION postgis_sfcgal;
user avatar
  • 1
13 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,621
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
0 votes

Split lines by polygons PostGIS

I had the same problem, and you're solution didn't fix it. I first found a workaround splitting the multilines twice : it worked better, but the split were not always precisely at the intersection ...
user avatar
  • 506
5 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.4k
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
0 votes

Getting base Line of PostGIS skeleton

A simple approach to "trim the branches" could be by longitude but is not a complete solution. with lines as ( SELECT st_subdivide( st_straightskeleton( ST_GeomFromText('POLYGON (( 190 190, ...
user avatar
  • 423
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
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
0 votes

Adding a polygon to a multipolygon column

Something using st_multi should do it: UPDATE mytable set geom = st_multi(polygon); should do it for you.
user avatar
  • 74.7k
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.3k
4 votes
Accepted

In PostGIS, how to identify intersecting line segments, but not matching start and end nodes

To find lines which intersect in the interior of both lines, a query is: CREATE TABLE intersection_points as SELECT ST_Intersection(a.geom, b.geom) AS intpt FROM a JOIN b ON ST_Intersects(a....
user avatar
  • 3,621
1 vote

Can't import a shapefile with different SRID in a geometry column

st_setsrid will return a new geometry with the updated projection. It is not an in-place update UPDATE myTable SET geom = st_setSRID(geom,4326);
user avatar
  • 34.4k
2 votes

Is an SRID unique?

EPSG:4326 isn't unique even for EPSG codes. Within the EPSG Geodetic parameter dataset, EPSG:4326 can either refer to a CRS or an Extent. EPSG:4326 (extent) EPSG:4326 (CRS) For uniqueness you need ...
user avatar
  • 11.8k
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
0 votes
Accepted

Insert a waypoint into a POSTGIS table with psycopg2 and ST_GeographyFromText

You need to enclose your Point into single quotes: SELECT ST_GeographyFromText('POINT(48.07093 11.19797)') -> vs SELECT ST_GeographyFromText(POINT(48.07093 11.19797)) ERROR: syntax error at or ...
user avatar
  • 6,698
0 votes

Geometry Viewer in pgAdmin 4 displays a straight line instead of polygons of European union countries

You probably need to change -- by using ST_Transform() -- the coordinate reference system (CRS) of your geometries, e.g. by using such query instead of a standard SELECT: SELECT ST_Transform(geom, ...
user avatar
  • 6,698
1 vote
Accepted

Calculating the distance For each Row with respect to a Common Point

It looks like 'port.longitude' and 'fac.latitude' are correctly replaced in your f-string, but 'Warehouse.longitude' and 'Warehouse.latitude' are not and are left as string. The reason is you try to ...
user avatar
  • 1,747
-1 votes

Load Shapefiles into a remote PostGIS db using command line?

const postGISdb = "database"; const user = "postgres"; const password = "password"; const host = "localhost"; const port = 5432; exec( ...
user avatar
0 votes

Counting stacked polygons in PostGIS

Let's assume there is a polygon layer called 'poly_test', see image below. Extending the idea from @geozelot it is possible to count the polygons via grouping by their geometry. With the following ...
user avatar
  • 23k
1 vote

Buffer with PostGIS in the form of a pentagon or hexagon

Not sure what you're asking. The title says pentagon or hexagon but "How would you buffer a point to a indivisiable 4 sided polygon." sounds like a square: select row_number() over() as id, ...
user avatar
  • 51.4k
0 votes

Saving a TIN given as a Wavefront OBJ file into PostGIS internal TIN format

As a DIY solution, after diving into the galaxy of 3D related stuff, I found a nice Python (>= 3.7) MIT licensed package called meshio (Schlömer, Nico. (2022). meshio: Tools for mesh files (v5.3.4)....
user avatar
  • 6,698
3 votes

Importing single-geometry data to postgis

This occurs due to the historic (and poor) decision to allow both multi and single geometries in the same Shapefile. Most database stores don't allow you to mix geometry types in a column so there is ...
user avatar
  • 74.7k
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
2 votes

PostGIS: get Circle to GeoJSON

As said in the comments, circle is a native postgres type that is not related to postgis. If using PostGIS, it is better avoid these native types. That being said, if you really have to use the circle ...
user avatar
  • 34.4k
0 votes

QGIS asks for PostgreSQL credentials multiple times

In my case, removing the "sslmode=disable" text from each y the .qgs file solved the issue. Looks like new layers added in my project do not add the sslmode param and old ones (maybe added ...
user avatar
  • 3,500
1 vote
Accepted

raster2pgsql no more INSERT/COPY statement in the log

On Linux (more info): raster2plpgsql [<options>] <raster> >> /dev/null On Windows (more info): raster2plpgsql.exe [<options>] <raster> > NUL
user avatar
  • 1,237
2 votes

undocumented PostGIS ST_Distance function taking text as parameters

Very short, but you should be able to understand it: postgres=# SELECT ST_Distance('POINT(0 0)','POINT(1 1)'); st_distance -------------------- 1.4142135623730951 (1 row) or more explicitly:...
user avatar
  • 6,698
6 votes
Accepted

undocumented PostGIS ST_Distance function taking text as parameters

The function ST_Distance(text, text) is defined by PostGIS. Here's the source code: -- Availability: 1.5.0 - this is just a hack to prevent unknown from causing ambiguous name because of geography ...
user avatar
  • 3,621
1 vote
Accepted

psycopg2 cursor.copy_expert() BadCopyFileFormat: extra data after last expected column when writing WKB data

The solution was to use .wkb_hex in order to have a plain string (not a binary one, which would have been OK though for a usage by ST_GeomFromWKB()): import psycopg2 from io import StringIO from ...
user avatar
  • 6,698
5 votes
Accepted

Rounding decimal numbers in PostGIS

Via Round ST_Distance you need to cast the values to a numeric: select name, round(ST_X(geom)::numeric, 2) as x_coordinate, round(ST_Y(geom)::numeric, 2) as y_coordinate from cities You could ...
user avatar
  • 8,163
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
0 votes

Should I use different projections to calculate distance and displaying on the Map?

From what I can gather of your question, you are mixing the display of the map with the output of the calculations, which will be a table. ...the output of the query... is tabular?? Use the ...
user avatar
  • 427
0 votes

Speeding up a TIN to MultiPolygonZ conversion in PostGIS

Atm was actually right in his comment. And indeed, it's actually a nonsense to tessellate a TIN! It was an error on my side. Here is a detailed comparison for a same query with and without the use of ...
user avatar
  • 6,698

Top 50 recent answers are included