Skip to main content

Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

Filter by
Sorted by
Tagged with
0 votes
0 answers
11 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
0 votes
0 answers
26 views

Geospatial with Redshift

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

Plugin or external function

I want to use two tables in QGIS with which I want to achieve the following. TabA should contain project names and TabB should contain log entries, i.e. a 1:n relationship. When entering a new log ...
FlyingEagle'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
38 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
  • 31
1 vote
0 answers
50 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
30 views

Layer from database taking long time to load on QGIS project

I have a layer in a PostgreSQL database that I put in a Qgis project. The problem is that it's taking a long time to load, despite having created an index for the geometry using gist and for other ...
vivjmin's user avatar
  • 135
5 votes
0 answers
195 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
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
2 votes
1 answer
64 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
1 vote
1 answer
37 views

Does PostgreSQL function concat_ws works as expected?

You find the documentation of the concat_ws PostgreSQL function here. It says this request concat_ws(',', 'abcde', 2, NULL, 22) results in this reply abcde,2,22. This example does not proves the ...
dancesWithCycles's user avatar
0 votes
1 answer
58 views

Get "not data owner" error when execute register with geodatabase even I connect with the owner user in ArcGIS Pro

I am using ArcGIS Pro and connecting to a PostgreSQL database. I have enabled the database as Enterprise Geodatabase and it automatically creates a user "sde" for my PostgreSQL database. ...
kgs-tliu's user avatar
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
31 views

ERROR 999999: Something unexpected caused the tool to fail && Error - Failed to execute (Manage Vector Tile Cache)

I'm trying to publish a web layer (which data come from a postgresql VIEW) in the arcgis entreprise portal. The web layer is published succesfully however the features are not visible in the portal, I ...
Jamal Samlani's user avatar
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
0 votes
2 answers
75 views

Getting clickable path using QGIS

I import into QGIS a PostgreSQL layer obtained from a simple select. This layer contains paths pointing to folders on my disk. I would like to be able to click on it from the QGIS attribute window and ...
fcka's user avatar
  • 985
0 votes
0 answers
24 views

Geoserver ImageMosaic with Postgres store instantiation

I am trying to set up an ImageMosaic store on a local instance of Geoserver. I set up a postgreSQL DB with Postgis extension for managing the images' info and tried to follow this documentation: https:...
Andreas Scalas's user avatar
5 votes
2 answers
247 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
0 votes
2 answers
74 views

QGIS writeAsVectorFormatV3 provides no useful errors

I am trying to write a layer as GeoPackage+symbology from my .qgz project file, which itself references the data in a PostGIS database but it is failing without any hint as to what's wrong. I've ...
floptical's user avatar
1 vote
0 answers
53 views

QGIS is not properly closing some PostgreSQL binary cursor, letting DB transactions with a state "idle in transaction"

In a QGIS 3.34.5-Prizren project having PostgreSQL/PostGIS layers, I can sometimes see such queries for different layers in the "Debugging/Development Tools" tab, under the "Query ...
swiss_knight's user avatar
  • 10.5k
1 vote
1 answer
59 views

PostGIS user privileges when working with QGIS projects and layers saved in a PostGIS database

I have a PostGIS database that includes multiple schemas, tables and a QGIS project. I have 3 general user roles: admin, editor and reader. I know of the issue with database connection information ...
geo_theo's user avatar
2 votes
1 answer
32 views

How to deal with concurrency when inserting polygons in a SQL table in a way that they don't intersect with each other

Problem: I have a table X, in which I am inserting polygons (ex: geometry data for a city). Before inserting them, I check if the input polygon intersects with any existing polygon in the table and ...
xorrax's user avatar
  • 21
5 votes
3 answers
158 views

Converting multipart MultiPolygon to self-intersecting polygon in PostGIS

Is it possible to convert a MultiPolygon to a polygon when the result is a self-intersecting polygon? I have a bunch of MultiPolygons that are essentially originally formed to be multipolygons because ...
ambivalent's user avatar
2 votes
1 answer
59 views

Changing geometry column data type in all tables of a schema

I have a database of almost 500 tables. For most of them, the type is set to 'GEOMETRY'. I need to switch the type to be POINT, LINE or POLYGON. Using this query : select f_table_name, ...
Abigail's user avatar
  • 81
1 vote
0 answers
61 views

QGIS triggers SQL queries involving ST_Intersects on non-geometric PostgreSQL tables, raising database errors

In QGIS 3.34.5, there are some wrong SQL queries in the log of the PostgreSQL database (13+3.4), each time I zoom in/out on a map having PostgreSQL layers, some of those being non-geometric tables: ...
swiss_knight's user avatar
  • 10.5k
0 votes
0 answers
30 views

Populate new Azure PostgreSQL DB from existing shapefile using QGIS

I have a fresh Azure PostgreSQL DB with PostGIS Extension. I am in QGIS and have made the connection and tested it. It seems fine. USING QGIS 3.34 I am using DB Manager to "Import Layer/File&...
Cary H's user avatar
  • 996
0 votes
0 answers
25 views

PostgreSQL - Shared road sections between two counties: how to dynamically show left and right county names according to zip code

I have table "network" containing data on a road network spread across several contiguous counties. Many road sections are shared between two counties (ie. left side belongs to county A and ...
dot314's user avatar
  • 51
5 votes
2 answers
192 views

Problem with ST_Difference in PostGIS

I'm testing various SQL queries to do a seemingly easy ST_Difference in PostGIS. It needs to be done in SQL as I'm testing to later use the query on quite large datasets. Original code I tried: CREATE ...
vivjmin's user avatar
  • 135
0 votes
0 answers
35 views

Delete item from pgSTAC - using psql or pypgstac

I'm working with a pgSTAC database and trying to figure out how to remove two specific items. I've loaded items using pypgstac, but didn't see a way to accomplish a delete operation with that tool. So,...
ebbishop's user avatar
  • 121
3 votes
1 answer
185 views

SRID in PostGIS -> ST_SRID !== Find_SRID

i am trying to understand why I get two different values for those two Querys (to a PostGIS db) SELECT ST_SRID(ground_geometry) FROM cjdb.city_object LIMIT 1; select Find_SRID('cjdb', 'city_object', '...
pcace's user avatar
  • 237
1 vote
0 answers
28 views

Error in QGIS when doing operations on raster data loaded from Postgres

I am trying to create a virtual raster in QGIS with raster data imported from a local Postgres database. I go Raster -> Miscellaneous -> Build Virtual Raster... and click Run. Here's the log ...
alex-gdv's user avatar
1 vote
0 answers
38 views

Upgrade from v13.2 to v13.13 breaks PostgreSQL triggers

We noticed that our table record geometry was not updating for spatial records with our defined special trigger. The trigger was not modified at any time after the PostgreSQL upgraded from 13.2 to 13....
user9491577's user avatar
3 votes
1 answer
52 views

Vertices table generated by pgr_createTopology() doesn't have enough vertices

I get 7639 points using st_startpoint and st_endpoint, however the vertices table generated by pgr_createTopology only have 2393 points. The red points are generated by pgr_createTopology, and the ...
Tom's user avatar
  • 31
0 votes
1 answer
49 views

Error importing shapefile into PostgreSQL using PostGIS

I installed both PostgreSQL and PostGIS. But I am not able to import a shapefile to the database? It gives an error, "Shapefile import failed". What am I doing wrong? I opened the file with ...
aggie's user avatar
  • 1
1 vote
0 answers
55 views

Calling ST_IsValid on a QgsGeometry in PyQGIS, referencing PyQGIS object in SQL statement

I'd like to be able to verify the validity of a geometry of type QgsGeometry within PyQGIS. From what I can understand, the steps are Convert QgsGeometry to something that ST_IsValid will recognize. ...
nr_aus's user avatar
  • 3,576
1 vote
1 answer
100 views

PostGIS crosstab error code [closed]

Can anyone tell me why this does not work? SQL error: SELECT * FROM crosstab( 'select town, rescode, proptype FROM "public.windhamCoParcels" WHERE rescode = 'C' or rescode = 'S' or ...
Frank's user avatar
  • 53
3 votes
0 answers
108 views

PostGIS 3D buffer along an axis

TL;DR In PostGIS, I'm searching the most efficient way to compute a 3D geometry extruded along an arbitrary axis given as a LineString Z in space (each coordinate varies along the axis). Explanation ...
swiss_knight's user avatar
  • 10.5k
3 votes
1 answer
99 views

Updating full table instead of matched record problem

I am writing a PostGIS query in which I have to update the geometry column in table A with the value of the geometry column in Table B. Table B contains only 300 Rows while Table A contains more than ...
Muhammad Kamran's user avatar
3 votes
1 answer
120 views

Find points within a box bound using PostGIS

I'm trying to use PostgreSQL and PostGIS to return locations within a specific box bound. Example User has a map view on Google Maps Google API returns box bounds using map.getBounds() - in my ...
Kenn Seangpachareonsub's user avatar
3 votes
0 answers
125 views

Effective points clustering using PostGIS

I want to implement clustering points like here in the last example(with big and small points): https://www.crunchydata.com/blog/postgis-clustering-with-dbscan I have a table in PostgreSQL with ...
hifichevymane's user avatar
0 votes
0 answers
55 views

FME - Specify schema name in PostgreSQL database writer (ArcSDE)

I am attempting to transfer data from a file geodatabase into an enterprise geodatabase (PostgreSQL) using FME. By default, the writer copies it into either the "public" schema or the "...
RMT's user avatar
  • 39
5 votes
1 answer
172 views

ST_Intersects gives GEOSIntersects TopologyException side location conflict on two valid geometries

I have two geometries, one is a GeometryCollection, the other is a Polygon, when using ST_IsValid both are true and ST_IsValidReason both put out Valid Geometry. When using both in a ST_Intersects it ...
Johan's user avatar
  • 53
1 vote
0 answers
47 views

pgr_nodeNetwork returns old_id as int and not bigint

I am using pgr_nodeNetwork('edges', 0.001, 'id', 'geom', 'noded', '', f) to create the paths network. Since the table is being re-generated multiple times the value of old_id in resulting table have ...
postgis-user's user avatar
0 votes
0 answers
15 views

Unable to setup PyCharm Geo Viewer, enable JCEF setting unavailable

Has anyone used the geo viewer in PyCharm? I was looking for an equivalent to the VS Code geo data viewer extension and found this documentation. However, when following the steps I cannot find the ...
William Etzler's user avatar
0 votes
1 answer
68 views

GDAL/OGR import file GDB to a specific PostgreSQL schema

I'm trying to import a dataset (file GDB) into a PostgreSQL (16) + PostGIS (3.4) database using GDAL 3.6.3 (released 2023/03/07) without any kind of success: ogr2ogr -f PostgreSQL "PG:${PG_URI}&...
swiss_knight's user avatar
  • 10.5k
0 votes
1 answer
76 views

PostGIS reader in FME: Error executing SQL command ('FETCH 10000 IN "public_table_crsr"'): 'ERROR: Invalid endian flag value encountered

In FME-form, when executing a vanilla PostGIS reader connected to a PostgreSQL + PostGIS table, I encounter this error: ERROR | Error executing SQL command ('FETCH 10000 IN "public_table_crsr&...
swiss_knight's user avatar
  • 10.5k

1
2 3 4 5
85