New answers tagged postgresql
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 ...
- 475
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 ...
- 164
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