Skip to main content

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
0 votes
0 answers
6 views

Where to find the pg_service.cnfg file for an existing QGIS project with postgresql connection? And how to make the file if the service name is blank?

I have an existing QGIS project to work on and the layers are from a postgresql connection. I want to open this project on QField on Android but it couldn't load the layers because it requires ...
AliZ.ece's user avatar
1 vote
0 answers
11 views

Geometry search by frustum using PostGIS 3.3.4

I have a table, that represents model elements: create table bim_geometry ( id SERIAL PRIMARY KEY, name VARCHAR(64), geom geometry(POLYHEDRALSURFACEZ, 0) ) I want to get elements that fit ...
Dmitry Kulakov's user avatar
0 votes
1 answer
23 views

Create point along a line from multiple records in PostgreSQL

I have a dataset of motor vehicle crashes in PostgreSQL, and some of them are missing latitude & longitude coordinates, but I have the location based on the milepost of the roadway the crash ...
Chris's user avatar
  • 91
1 vote
0 answers
20 views

Loading result of PostGIS query that contains temporary table delete statements as new layer

I am trying to run then load the result of a PostGIS query using the DB Manager on QGIS 3.30.2. The query is a call to a PostGIS custom function defined in the database that generates some polygons. ...
Boris CALVET's user avatar
0 votes
1 answer
24 views

Tiles getting generated in both PostgreSQL and gwc directory?

I have a GeoServer 2.21.0 installed on EC2 instance with Ubuntu server. Initially I tried creating tiles in with default blob storage on file system which is the geoserver/data_dir/gwc directory. Soon ...
Gurminder Bharani's user avatar
0 votes
0 answers
25 views

Function addgeometrycolumn does not exist in QGIS Model designer

I'm using Export to PostgreSQL algorythm in QGIS Model Designer to load my layer into PostGIS database. You can see the parameters on the screenshot. I manage to connect to the database after I run ...
barney stinson 2's user avatar
1 vote
0 answers
25 views

Is there a clear use case for partition by in ST_ClusterDBSCAN?

If I want to create clusters of point, what would be the use case for adding PARTITION BY some columb to ST_ClusterDBSCAN function? I understand if I want clusters within states, e.g. In that case ...
tlzg's user avatar
  • 161
0 votes
0 answers
27 views

Geospatial with Redshift [closed]

As AWS Redshift is based on PostgreSQL are the spatial capabilities in Redshift as comprehensive as the spatial capabilities of Postgres? What do I need to be aware of when using Redshift as a data ...
eponkratova's user avatar
1 vote
1 answer
28 views

gpkg filename to add PostGIS table field

Is there a way in ogr2ogr to add gpkg filename to a PostGIS table existing field (filename)? during append? ogr2ogr -f "PostgreSQL" PG:"host='ip' port='port' user='user' dbname='db' ...
Tamas Kosa's user avatar
  • 1,509
1 vote
1 answer
41 views

How to modify a feature's JSON attribute on PostGIS with QGIS

I want to edit (with QGIS) an attribute of my feature stored in PostGIS. First, I select the feature using one of the buttons below. Then I use the edit feature button (Modify the attributes of all ...
sutan's user avatar
  • 951
1 vote
0 answers
25 views

Could not find libgeos_c

I'm running ./configure --host=arm-linux-gnueabihf to compile and install PostGIS, but I'm getting the following error, I don't know why this is happening. GEOS version = 3.12.2 PostGIS version =3.4....
user249876's user avatar
1 vote
0 answers
49 views

Get data about rendered entities (mapnik) for each zoom

I'm using mapnik (mod_tile + renderd) to generate tiles based on data from PostGIS. The data is very simple, several countries with roads, names, etc. There is a style description file (xml), where ...
Ezhidze's user avatar
  • 11
1 vote
0 answers
46 views

Create PostGIS column of a specific H3 hexID/resolution

I've used H3 in the past just for speeding up my spatial queries, however I'm looking for the best way to store the index information itself alongside my data. Something like: CREATE COLUMN H3res14 --&...
Lucian B's user avatar
-1 votes
0 answers
27 views

Rendering basemap + polygon as JPG and sharing through email

I currently have a PostgreSQL/PostGIS (versions 14/3.2) instance with several tables. When adding a new polygon through QGIS, the server sends an email with info about said layer (using listen/notify ...
d_araujo's user avatar
3 votes
1 answer
103 views

Merging PostGIS tables with billions of rows

I'm trying to create a unified high population density dataset using the Facebook "data for good" dataset. Currently their dataset is available as a raster or CSV per catagory (men, women, ...
RedM's user avatar
  • 1,565
-1 votes
0 answers
39 views

Optimizing GeoServer and PostGIS for High-Concurrency Survey on AWS

To manage performance and scalability for a survey system involving 600 concurrent users, hosted on an EC2 AWS instance with a PostGIS database and GeoServer, and planned for online surveying using ...
user248222's user avatar
1 vote
0 answers
68 views

PostGIS index not being used

I am trying to create a database of UK OAs (Output Areas) and their geometry data. There are 180,000 OAs. The max vertice count for a row is 10k. Each record contains either Polygon or MultiPolygon ...
Brenwell's user avatar
  • 111
2 votes
1 answer
47 views

Creating smaller equal sized and orientied polygons in existing grid polygons

I have a grid of large polygons that I would like to split into 16 equal sized polygons of dimension ~402.33 m x ~402.33 m (Picture 1). The epsg is 26910. The script written does that, however, the ...
MJM's user avatar
  • 995
0 votes
0 answers
17 views

GeoServer does not fill native SRS for PostGIS layer

When creating a new vector layer from a PostGIS JNDI datasource, the field Native SRS keeps being empty. Declared SRS is correctly set to EPSG:4326. I know there already exist some posts on this topic ...
ulrich's user avatar
  • 1,170
0 votes
1 answer
28 views

ogr2ogr from PostgreSQL-Installer on Windows without PostgreSQL-Driver

I have a local installation of PostgreSQL 16 on a windows-Computer from EBD (https://www.enterprisedb.com/downloads/postgres-postgresql-downloads). I am struggling with the ogr2ogr tool when exporting ...
leole's user avatar
  • 317
0 votes
0 answers
22 views

Connecting to database I host with QGIS Cloud

I'm interested to learn whether it is possible to use my own PostGIS database that I host with QGIS Cloud rather than keep paying for more storage from QGIS Cloud?
AWGIS's user avatar
  • 3,230
1 vote
1 answer
38 views

"addr:street" seems to be missing after using osm2pgsql to load data into postGIS

I have some geographic usecases for a personal usage (i.e. real-estate price forecast), and after digging through different tools, I wanted to learn more about PostGIS. osm2pgsql seemed like a great ...
Volantis's user avatar
4 votes
1 answer
97 views

PostGIS ST_ClusterDBSCAN returns NULL

I have problems with the ST_ClusterDBSCAN function in PostGIS. I try to group points derived from lines with a maximum distance of 15 meters from each other and a minimum cluster size of 4. This ...
mapmap's user avatar
  • 53
1 vote
0 answers
39 views

Save metadata from QGIS directly to PostGIS database

When I save a layer in a geopackage in QGIS, the metadata is also saved. When I look at the layersettings in the geopackage, all metadata is visible. However, when I import the layer into a PostGIS ...
MmVi's user avatar
  • 33
1 vote
0 answers
51 views

Improve performance of PostgreSQL query function [closed]

I have a server (pysical server, AMD Ryzen 7 PRO 8700GE (5.1Ghz)) , 64gbram, raid0 nvme) here which runs a PostgreSQL/PostGIS server serving around 60.000.000 datapoints of data like this: id::text ...
pcace's user avatar
  • 237
0 votes
0 answers
25 views

osm2pgsql error: Connecting to database failed (context=check)

I got this error while importing an osm file to PostgreSQL directly. My code is: osm2pgsql –c –d highway -U postgres –P postgres –W postgres -H localhost "D:\data\highway.osm" The result ...
Yến Giang's user avatar
2 votes
1 answer
55 views

Exporting polygons to existing database in QGIS

I have exported a shp polygon file in QGIS to a table in pgAdmin. Now I have more polygons in QGIS that I want to export to the same table in pgAdmin. This means that I want to add the new polygons to ...
Alexi's user avatar
  • 71
2 votes
1 answer
102 views

ST_AsMvtGeom returns null

I have a line that is produced in a bit strange way. I don't want to go into details how it is produced and why that way. I would just like to understand the problem. So, the line starts at point A, ...
tlzg's user avatar
  • 161
5 votes
0 answers
198 views

How do I manage user privileges when working with a QGIS project stored in a PostGIS database?

I have set up a PostGIS database (PostgreSQL 15.5, PostGIS 3.4, QGIS 3.34 LTR) which 2 different user groups can access: editor and reader. For simplicity, let's say there are 3 schemas in the ...
geo_theo's user avatar
4 votes
1 answer
59 views

QGIS Virtual Layer Query containing JOINS always ends up having zero results

I've got a problem JOINing together several PostGIS tables (with or without geometry) in a Virtual Layer in QGIS 3.36.3 As soon as I use a (inner) join the the layer query statement, the result set is ...
Tom's user avatar
  • 53
4 votes
1 answer
82 views

Can I edit the EWKB format to use only 4 bytes for coordinate values instead of 8 bytes in PostGIS?

I am using PostgreSQL with PostGIS to store a large amount of polygons. I want to minimize the space my polygons are taking up in my database and since all the coordinate values of the polygons I am ...
L K's user avatar
  • 49
1 vote
0 answers
32 views

PostGIS: Custom projection matrix from known points

I have a PNG image of a map (mercator) and known points in a table like this: coord_map - geometry(Point, 0) coord_real - geometry(Point, 4326) 010100000000000000000000000000000000000000 (0,0) ...
JaFu0815's user avatar
2 votes
1 answer
65 views

Segmentation fault on ST_ConcaveHull call [closed]

I have a problem on creating a polygon for a geometry by using the ST_ConcaveHull algorithm. Following line geometry creates a segmentation fault and breaks the connection to my Java application. ...
Bora's user avatar
  • 23
0 votes
0 answers
33 views

PostGIS: DEALLOCATE addfeatures returned 7 [ERROR: prepared statement "addfeatures" does not exist when using pgbouncer connection pool

I have the same issue/question like this one: PostGIS: DEALLOCATE addfeatures returned 7 [ERROR: prepared statement "addfeatures" does not exist The error message copied from the QGIS debug ...
user1866929's user avatar
0 votes
0 answers
60 views

Handling large geospatial datasets for dynamic WebGIS application with PostGIS, Mapbox

I'm developing a WebGIS application that displays snow depth data for ski slopes using PostGIS, Mapbox, and Martin. My workflow is as follows: Process LiDAR data to produce GeoTIFFs of snow depth. ...
Nicolas Guibal's user avatar
1 vote
1 answer
23 views

GDAL org2ogr ESRIJSON Feature Paging

I'm trying to load some data from an Esri Feature Server URL into PostGIS, using ogr2ogr, but seem to be facing some issues with paging. The below code should in theory load all the Statutory Main ...
Tim R's user avatar
  • 33
1 vote
1 answer
41 views

GDAL ogr2ogr ESRIJSON to PG Date Formatting

I'm trying to load some data from an Esri Feature Server URL into PostGIS, using ogr2ogr but seem to be facing some issues with the date formats. The below code should in theory load all the World ...
Tim R's user avatar
  • 33
0 votes
0 answers
41 views

QGIS-server password for PostGIS

I am looking into using QGIS Server, I have gotten the OSM example up and running on a docker instance, but I want to run it with some data sets that I have stored in a PostGIS database. I made a ...
MortenSickel's user avatar
  • 3,834
0 votes
1 answer
55 views

QField and PostGIS on iOS

I have no issues with setting up a QField project with PostGIS layer on Android. However, I can't do it with for an iPhone, even though I've put the pg_service.conf file on QField root and auth ...
CAN84's user avatar
  • 121
1 vote
1 answer
36 views

ogr2ogr gives an overflow error

I'm trying to use ogr2ogr to import shapefile data in my PostgreSQL database. The MAC command line I use for this is export PGPASSWORD=xxx* ogr2ogr -skipfailures -nln xx_table -f PostgreSQL PG:"...
Thomas's user avatar
  • 11
0 votes
0 answers
53 views

PostgreSQL error when editing PostGIS layer in Lizmap

I've installed lizmap web client 3.6.12 on a Debian 12.5 machine. I try to edit a layer via lizmap. I've installed PostGIS extension this way: https://trac.osgeo.org/postgis/wiki/...
Konsole's user avatar
  • 11
1 vote
0 answers
68 views

Spatial indexes not being used despite existing

Here is my query: explain analyze with pings as ( -- ================= SELECTING JOINED data with join conditions -- ================= -- ================= -- ================= -- ================= -- ...
analyst92's user avatar
  • 857
2 votes
0 answers
66 views

Remove side branches from linestring

I'm trying to create a centerline from a (curved) polygon. To do this I'm using the PostGIS ST_ApproximateMedialAxis tool. However, this creates a lot of side branches, whereas I only want to keep the ...
eve's user avatar
  • 21
2 votes
0 answers
38 views

Managing amount of DB connections of QgsDataSourceUri with PyQGIS

So basically I have a simple code that adds vector layers to the QGIS project from PostgreSQL. self.uri = QgsDataSourceUri() self.uri.setConnection( self.host, self.port, self.db, self....
silenceqq's user avatar
1 vote
1 answer
119 views

Fastest way to clip millions of small polygons and storing results as JSON [closed]

I have a dataset with couple million farm polygons (around 2.5 million and only expected to grow in the future), and I need to calculate the NDVI for these farm polygons, with some basic stats (...
Hasan Mustafa's user avatar
5 votes
2 answers
248 views

Difference between datatypes GEOMETRY and GEOMETRY(GeometryZ, 32618) in PostGIS

I have just started working with PostGIS, Just curious which is the correct format to store 3D data in PostGIS, Although both are giving same results: CREATE TABLE buildings_3d ( id Int ...
Shoaib's user avatar
  • 85
2 votes
1 answer
113 views

Does ST_Simplify help on lines?

I have a function that selects all lines whose geometry fall into given tile envelope. There are a couple millions of lines with proper indices on the table. Would the query perform better if I would ...
tlzg's user avatar
  • 161
0 votes
1 answer
91 views

Union of vector polygons split on tile boundaries

I have two geopackages - vectorized output from a classification exercise (let's call them tile1.gpkg and tile2.gpkg). These are located on the border between the two tiles like so: Each of these has ...
Momchill's user avatar
  • 294
1 vote
0 answers
34 views

Import shapefile using ogr2ogr with a subselect to fill a constraint fails to "cannot open datasource"

I try to import various shapefiles into a postgis database. The shapefiles have some foreign keys referencing other existing (and already successfully imported) tables. ogr2ogr -f "$($json....
semTex's user avatar
  • 111
1 vote
0 answers
35 views

PostGIS: Left Join (ST_intersects) lead to multiple counts (not distinct) in aggregate function [duplicate]

I have a query with 2 left joints (ON ST_Within, ON ST_Intersects).The second left join (ST_Intersects) in the example below causes multiple counts of the point_table in the aggregate function (SUM). ...
teamspeakuser's user avatar

1
2 3 4 5
190