New answers tagged

1 vote

ST_ConcaveHull() is giving strange results in some specific cases

The Concave Hull is computed based on the Delaunay Triangulation of the input vertices. The discrepancy in this case is caused by a different DT being produced due to the change in location of the ...
  • 4,031
0 votes

Sew lines with tiny deviations due to numerical inaccuracy to construct a concave shaped polygon

You can move st_dump to a subquery: WITH cte AS ( SELECT st_makeline(geom) as line FROM ( select (ST_Dump( ST_GeomFromText( 'MultiLineStringZ ( ...
  • 36.1k
0 votes

Get geohash neighbors with PostGIS

Sorry, I'm 5 years late... A reliable solution. Based on this old Javascript of movable-type.co.uk/scripts, that was based this oldest (2008) library, https://github.com/davetroy/geohash-js Pure ...
  • 2,154
0 votes

SQL query to have a complete geojson feature from PostGIS?

For me the solutions didn't worked with PostGIS 3.2.1 and PostgreSQL 14.5. But in the current docs of PostGIS for ST_AsGeoJSON, there is an even simpler solution with json_agg for this problem. ...
  • 1
2 votes
Accepted

Calculating speed at various points along a route for each device

Arithmetic on two TIMESTAMP types return an INTERVAL type - use their EPOCH second representations instead: ( EXTRACT(EPOCH FROM time_created) - EXTRACT(EPOCH FROM LAG(time_created) OVER(PARTITION BY ...
  • 25.1k
0 votes

Importing geopackages (epsg:900913) into PostgreSQL/PostGIS shows up as 900914

I had a similar problem using org2ogr from GDAL 3.4 version. I guess something has changed in GDAL in the 3.4 version. GDAL identifying SRID from prj in shapefiles
1 vote

Use PostGIS binary function on all possible pairs

Join the table to itself on intersects, where id's are not the same: select a.id a_id, b.id b_id from public.opbuff a join public.opbuff b on st_intersects(a.geom, b.geom) where a.id<b.id
  • 55.3k
1 vote

Trigger to fill attribute table

If you want to use a database trigger you need to write it in PL/pgSQL or another language supported on your database server. If what you want to achieve is to concatenate all field_value values when ...
  • 3,682
0 votes

Same Spatial Query on nodes table gives different read time

Are your 2 tables really identical ? The second one has a geom of type Geometry whereas the first has the type Point so maybe the content is not the same ? Also, for performance, a lot of things can ...
  • 2,022
3 votes
Accepted

converting geometry of SRID 0 to geography

A GEOGRAPHY type instantiation assumes EPSG:4326 by default, so you can simply cast your GEOMETRY types, and even get correct results if they actually are referenced in EPSG:4326. However, it is ...
  • 25.1k
1 vote
Accepted

Joining tables with GROUP BY in ArcGIS Pro

So the key to working with geometry and grouping is to avoid it -- only get the stuff you need up front in a virtual table, then go back for whatever else you need via JOIN. SELECT vt.objectid, ...
  • 19.1k
2 votes
Accepted

Merge lines that intersect postgis

I fixed this by using: ST_LongestLine((ST_CollectionExtract(unnest(ST_ClusterIntersecting(nl)))), (ST_CollectionExtract(unnest(ST_ClusterIntersecting(nl))))) instead of: ST_CollectionExtract(unnest(...
  • 402
0 votes

QGIS "Failed to add field" to PostGIS table

I faced a similar situation in QGIS where I could add new features and save them, but adding or deleting new fields weren't possible. It turned our that my logged in user in QGIS had permissions for ...
  • 101
0 votes

Using ST_Intersects inside of an ST_Within

You're just doing 1 thing too many. All you need is st_intersects with the new geom: NEW.col1 = ( SELECT count(*) FROM t1 JOIN NEW ON ST_Intersects(NEW.geom, t1.geom)); If I have ...
  • 6,584
1 vote

Find nearest point of interest for each category in PostgreSQL

When doing "nearest" queries it's most efficient to use the PostGIS Nearest-Neighbour functionality. To find the nearest neighbour in each group a separate (internal) query is required. ...
  • 4,031
0 votes

Find nearest point of interest for each category in PostgreSQL

It's better to normalize the data structure first. In an existing structure, try: SELECT p.id, p.category, y.distance FROM ( SELECT min(o.id) as id, c.cat_type, x.distance FROM ( SELECT ...
0 votes

ERROR: function st_hexagongrid(integer, geometry) does not exist

The error occurs because you are passing as parameters an integer and a geometry, and the function requires a float and a geometry. ST_HexagonGrid(float8 size, geometry bounds); change to: SELECT ...
0 votes
Accepted

Find all objects within 3D volume

These are the 3d functions available to you: ST_3DClosestPoint — Returns the 3-dimensional point on g1 that is closest to g2. This is the first point of the 3D shortest line. ST_3DDistance — For ...
  • 6,584
1 vote
Accepted

lw_geom_to_geojson: 'CompoundCurve' geometry not supported

The GeoJSON specification does not allow curved geometries. See https://geojson.org/ GeoJSON supports the following geometry types: Point, LineString, Polygon, MultiPoint, MultiLineString, and ...
  • 54.1k
2 votes

How to correct the connection factory error in GeoServer when connecting to PostgreSQL

GeoServer needs to be able to open a connection to the remote postgis server. You need to make sure that there is an entry in the pg_hba.conf file in the same way as for any other program. You should ...
  • 76k

Top 50 recent answers are included