Questions tagged [postgis]

PostGIS is an extension for the PostgreSQL object-relational database that adds support for geographic objects.

Filter by
Sorted by
Tagged with
3
votes
1answer
36 views

ST_3DIntersection with LINESTRING M and POLYGON M

I have a database of aircraft positions and I'm trying to develop a way to recreate what airspace looked like at a time T. So my idea was to store each pair of positions as a LINESTRING M(lon1 lat1 t1,...
3
votes
1answer
39 views

Union multiple LineStrings and convert MultiLineString to Linestring

I have three LineStrings which I want to ST_Union() by a column id to become one LineString. SELECT id, ST_Union(geom) geom FROM lines GROUP BY id The resulting geom however, is of type ...
0
votes
0answers
20 views

Add geometry to XYZ-tiles in .JPG format to serve in PostGIS

I have a WMS service from a governmental website of my state. Using the QGIS plugin QuickMapServices and its function Generate XYZ Tiles (Directory) in the "Processing toolbox" I downloaded ...
2
votes
1answer
40 views

Geometry data type Binary storage description

I'm looking for some low level information or reading or reference material which explains how most GIS databases store the geometry data type in binary form, because - I never really investigated it ...
1
vote
1answer
48 views

Cannot create PostGIS data store in GeoServer

I am trying to add a PostGIS database a data store in my GeoServer installation but I keep getting this error: Error creating data store, check the parameters. Error message: Unable to obtain ...
4
votes
1answer
74 views

Compound contiguous links to lines in a network and find isolated lines

I want to compound the red and green links (lines between the poinst) to one or more lines. I need it to finde the green links that not touching the red lines in my network. Step 1: compound touching ...
2
votes
1answer
72 views

QGIS slow performance with PostgreSQL/PostGIS

We experience slow performance in QGIS 3.xx (also tried with 3.20 with no noticable improvement). Data are stored in a PostgreSQL/PostGIS database and we have done enough testing that we do not ...
2
votes
1answer
62 views

Overlapping Lines on Vector

Using PostGIS my objective is to find overlapping lines sitting on a vector. While this query works as expected. SELECT ST_Covers( ST_GeomFromText('LineString (0 0 , 2 2)'), ST_GeomFromText('...
3
votes
0answers
44 views

Transform detached MultiLineString to LineString by adding connection lines between each line segment in PostGIS

I have MulitLineStrings that may consist of several detached lines like these three: What I need is to transform it to a LineString. For my use case it would be the best strategy if I could connect ...
3
votes
1answer
51 views

Finding isolated links in a network of links with PostgreSQL

I have a street network from many of links in a PostgreSQL table. Some links have no connection to the network. I want to isolate these links from the network. Each link has a geometry (geom) and id ...
1
vote
0answers
55 views

Importing to pgsql from gpkg/shp with ogr2ogr

I am trying to import features from a geopackage (or a shapefile) in an existing table of a distant PostgreSQL database. I use ogr2ogr which I run from OSGeo4W Shell. The code used with the geopackage ...
0
votes
0answers
26 views

ogr2ogr to CSV with quotes breaks with NULL values

I have some data stored in a Postgres 13 Postgis 3.1 table that looks like id(integer),valid_to(timestamp),language(varchar),name(varchar) 1,2021/08/16 11:19:06.493,eng,London 2,2021/08/16 11:19:06....
4
votes
1answer
75 views

PostGIS clustering with statistics

I'm discovering PostGIS for myself and right now I'm stuck at the point with clusters. Found some tips about how to make clusters on point layer in PostGIS by using ST_ClusterWithin function. Right ...
2
votes
2answers
39 views

Get the subline of a LineString consisting of all intersecting POINTS without knowing start and ending node in PostGIS?

I have a a set of LineStrings in PostGIS just like this one (could be of any, not-self-intersecting shape): I then get intersecting POINTs on that line like this: What I would like to get now is the ...
2
votes
1answer
40 views

How to select polygons that don't intersect with line but with its buffer?

I have lines and polygons in a PostGIS database. The lines may intersect with these polygons. I want to select all polygons that do not intersect with a line but do intersect with its buffered area. ...
11
votes
4answers
173 views

PostGIS ST_ConvexHull() but perpendicular to a line?

I have a rather special requirement for a PostGIS geometry manipulation. Consider the following case: I have a polygon and a line feature. If I do a ST_ConvexHull() or ST_ConcaveHull() I would get ...
3
votes
2answers
45 views

Get max value from points based on distinct id, and keep geometry for the new selection

I have a pointfile of approx.. 226000 points, where I need to filter by max value and distinct id_pol. SELECT distinct (id_pol), MAX(value) FROM fillspill_pol_value GROUP BY id_pol; This ...
6
votes
0answers
67 views

Generate closest non-intersecting polygon

I have a PostGIS database of polygons representing regions in a 2D space and I'm trying to find a way to generate the closest square of a given size to a given region that doesn't intersect with any ...
3
votes
1answer
74 views

ogr2ogr append script keeps incrementing id field in PostgreSQL/PostGIS table

I have a geopackage (from the 'input' app) which is being inserted into a postgres/postGIS table via an ogr2ogr script on a daily basis: ogr2ogr -append -f "PostgreSQL" "PG:user=xxxx ...
6
votes
2answers
228 views

Is it possible to run a PostGIS query on a SpatiaLite database?

I like using SpatiaLite because, for me, it's easier to manage files on my computer instead of having tables inside a PostgreSQL database. However, there are sometimes I need PostGIS functions that I ...
2
votes
1answer
50 views

PostGIS trigger : table and materialized view

I have a PostGIS polygons table for my "cities". I have a point table for my "boreholl". I created a "mv" materialized view defined by a spatial join as follows: SELECT ...
2
votes
0answers
41 views

Clustering spatial data in database

I have a simple website that is hosted on 000webhost. This service provides storing data in MySql database for a free plan. I wanted to extend some functionality for my site by clustering loads of ...
7
votes
1answer
126 views

Performant possible pairs in concave polygon

My objective is to create all possible pairs of connecting vertices in a concave polygon, i.e. only keeping those lines which are covered entirely by the polygon itself. I have come up with the ...
2
votes
2answers
36 views

Distance error with pgr_withPointsCost

Using pgrouting 3.1, I'm getting a error when doing a many to many calculation. I've reduced it to a one to many to try and find out what I'm doing wrong. So with a starting point (green) [33] and end ...
1
vote
0answers
17 views

Export part of schema to SpatiaLite

I want to export a part of a schema to SpatiaLite. It seems that I am to be able to export the whole schema with gr2ogr -skipfailures --config PG_LIST_ALL_TABLES YES --config PG_SKIP_VIEWS YES -f &...
0
votes
2answers
31 views

AUTHORITY:NUMBER error in GeoServer request with bbox filter

I am trying to get some features from GeoServer if they are inside a certain area, after making a request to the local GeoServer I get the following error: org.opengis.referencing....
0
votes
1answer
42 views

Implementing PostgreSQL table trigger with GeoServer JDBC user service in Leaflet app

I've been using a table trigger on a PostGIS table to track changes for some time now -- it saves username, date, time, etc in a history table (based on this PostGIS doc). Now, I'm developing a ...
3
votes
0answers
47 views

Counting stacked polygons

In my data set I have cases where I have multiple polygons stacked upon top of each other. I assume that those polygons have equal geometries but since there are a lot of them I am not sure 100%. Data ...
0
votes
1answer
20 views

Change / Set LayerId of GeoWebCache Layer (GWC inside GeoServer)

I am currently creating a Docker Container to Pre-Seed GeoWebCache caches. The idea is to spin up the Docker Container(s) to create the caches when there are new Orthophotos and kill them when done ...
2
votes
0answers
38 views

PostGIS "thresholded intersect" or "signfiicant intersect" query

I've been working an application using PostGIS that needs to join the interior intersection of two different layers (made up of polygons and multipolygons). These layers have many shapes which are ...
1
vote
1answer
25 views

How to format PostgeSQL query results in PHP to return GeoJSON object?

I'm building a small map application with Leaflet and PostGIS. I'm having an issue similar to this post where using pg_fetch_all is returning the data I want, but buried in an associative array. When ...
6
votes
3answers
338 views

Invert the ST_DWithin() function

Is it possible to perform a query like this: SELECT * FROM foo JOIN bar ON ST_Dwithin(foo.geom, bar.geom, > 10); -- the > 10 means that I want to select everything that is ...
0
votes
0answers
26 views

Fastest way to generate PNG tiles from a KML file

I have several KML files and each KML is being parsed, turned into a geometry with PostGIS' ST_GeomFromKML() and saved to a database. This database is parsed and uses ST_AsPng() to process each ...
1
vote
0answers
59 views

Data in QGIS getting deleted

I have been encountering an issue with QGIS that the data encoded on a layer connected to the database via PostGIS layers are getting deleted. It happened twice now. Database is in pgAdmin4 and is ...
2
votes
1answer
50 views

Spatialite - equivalent of ST_AsMVT

I managed to make vector tile server in Java, using some libs, and simple code. In first example, I used PostGIS and example of it is this code: public byte[] vratiNestoPostGis(Integer z, Integer x, ...
5
votes
1answer
58 views

Where are the WKT types CIRCULARSTRING, COMPOUNDCURVE etc actually defined?

I'm trying to find the actual definition of the curve types supported by PostGIS in WKT. PostGIS's documentation refers to the " SQL Multimedia Applications Spatial specification": The SQL ...
2
votes
0answers
100 views

Generate a TIN or DEM with soft breaklines in qgis, postgis or grass

I have 3 datasets to use as input for a digital elevation model contours (3d line) points (3d point) breakline (2d line, in blue) The third input, the breakline do not have an elevation value or ...
2
votes
1answer
59 views

PostGIS - st_intersects: WHERE vs INNER JOIN

In PostgreSQL/PostGIS, when running an intersection between 2 geometries, what the difference between these two syntaxes ? -- Method 1 SELECT a.id b.id FROM a, b WHERE st_intersects(a.geom, b.geom) --...
2
votes
1answer
42 views

Checking if boundaries of two geometries touch using PostGIS

I am fiddling around with this dataset http://s3.cleverelephant.ca/postgis-workshop-2020.zip. It is used in this workshop http://postgis.net/workshops/postgis-intro/spatial_relationships.html. I have ...
3
votes
2answers
65 views

ST_Disjoint() gives unexpected result using PostGIS

I am fiddling around with this dataset http://s3.cleverelephant.ca/postgis-workshop-2020.zip. It is used in this workshop http://postgis.net/workshops/postgis-intro/spatial_relationships.html. I want ...
3
votes
1answer
36 views

Select multiple rows from PostGIS table where ST_Contains has multiple manual inputs

Under normal circumstances I could get the spatial intersection between a table with multipolygons and another table with point geometry using some variant of SELECT * FROM ( SELECT * FROM ...
1
vote
2answers
114 views

WKB structure does not match expected size

I'm running an Alteryx workflow where I am pulling data from one database to another PostgreSQL database containg PostGIS. However, when I run the workflow and write the data to the database, I am ...
0
votes
1answer
42 views

ogr2ogr PostgreSQL layer creation options

Using ogr2ogr to import shapefile into PostGIS, I would like to be able to use specific SQL statements (ex: create index, alter column, add constraint, ...) within my ogr2ogr command line, to be ...
0
votes
1answer
29 views

Multiple GeoWebcaches (Docker) for one GeoServer

We are facing the problem that we have multiple projects with multiple high-resolution orthophotos from drones (1 photo > 2GB). We are using Geoserver with the integrated GeoWebCache. As a storage ...
2
votes
0answers
29 views

Importing pg_featureserv services into ArcGIS Pro as WFS layer

I'm currently working with pg_featureserv. I have successfully added the layer to QGIS using this tutorial. I have not, however, been able to add the service to ArcGIS as a WFS layer. Has anyone had ...
1
vote
1answer
42 views

Indicator of ST_Intersection processing time

Intersections on spatial datasets are slow. Inspired by this question intersection in postgis takes hours and hours to run, why? and Paul's post here, https://blog.cleverelephant.ca/2019/11/subdivide....
0
votes
1answer
35 views

lat/long lookup in GeoJSON regions provided by US state governments

I have polygon data (usually GeoJSON format) for various city and county boundaries from several US state governments. Example: New Mexico tax districts. Given a lat/long coordinate, I need to figure ...
2
votes
1answer
27 views

st_collect on multipoints

I have a geometry column that stores multipoints. How can I aggregate multipoints having another multipoint as a result, not a geometrycollection? select st_collect(geom), id from my_table group by id ...
2
votes
1answer
32 views

GiST indexing on constantly updating PostGIS Raster table best practice

I have a single PostGIS raster (3.x) table that holds multiple dates tiled up. I am constantly adding to this table via raster2pgsql. I want to keep the GiST index updated so I attach the -I flag but ...
0
votes
0answers
51 views

GeoDjango PointField | Sending coordinates using JSON

So I have this problem in my app. When I try to create user that have attribute "location" that is a GeoDjango PointField, only value set as 'default' is saved... When I send request with ...