Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

Filter by
Sorted by
Tagged with
1 vote
0 answers
18 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
  • 11
4 votes
3 answers
101 views

How to convert multi-part multipolygon to self-intersecting polygon in PostGIS

Is it possible to convert multipolygon to polygon when the result is self-intersecting polygon. I have bunch of of multipolygons that are essentially orginally formed to be multipolygons because they ...
ambivalent's user avatar
1 vote
1 answer
45 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
  • 71
1 vote
0 answers
53 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.3k
0 votes
0 answers
23 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
  • 964
0 votes
0 answers
24 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
3 votes
2 answers
156 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
  • 101
0 votes
0 answers
11 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
144 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
  • 183
0 votes
0 answers
19 views

How to resolve ImageMosaic JDBC plugin not functioning in GeoServer 2.24.1 [closed]

I'm experiencing challenges with publishing raster TIFF images stored in PostgreSQL using GeoServer 2.24.1 on Rocky Linux 9.3. After successfully importing the raster TIFF into PostgreSQL 15.6, I'm ...
Naina's user avatar
  • 1
1 vote
0 answers
19 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
36 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
41 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
0 answers
41 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
47 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,535
1 vote
1 answer
44 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
87 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.3k
3 votes
1 answer
88 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
64 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
83 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
27 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
95 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
10 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
49 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.3k
0 votes
1 answer
48 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.3k
0 votes
0 answers
29 views

Layer(s) not valid using QGIS WMS Server

I am trying to setup a QGIS WMS Server and populate it with data from my PostgreSQL database but I keep getting CRITICAL Server[45873]: Error, Layer(s) ...
Nathan L.'s user avatar
2 votes
1 answer
52 views

ST_OrientedEvelope returning parallelogram instead of rectangle

I have a GIS application with multiple polygons where I'd like to get the bounding box of the polygons. The ST_Envelope function works as expected. Because the map is rotated the polygons always ...
Ricardo Mayerhofer's user avatar
0 votes
0 answers
66 views

AutoCAD Map3D New feature from geometry fails on PostGIS

I am designing a workflow for my company which would include connecting to a PostGIS database from AutoCAD Map3D. This is done via the FDO API, included in Map3D. A key functionality I want to use is ...
crafter6029's user avatar
0 votes
2 answers
42 views

Import of GeoJSON Multi Polygon into PostgreSQL does not work with GDAL ogr2ogr anymore

Original Post I imported this Multi Polygon ./apps/ogrinfo -so -al ~/Downloads/osm-boundaries/germany/OSMB-5cf5ed195895ffb3c65abc341dc8138bb5c6cedf.geojson/OSMB-...
dancesWithCycles's user avatar
0 votes
1 answer
29 views

Delete Features of a layer from PostgreSQL on QGIS itself

I have layers that I need to edit using QGIS that I import from my company's PostgreSQL server. I currently have the ability to add features, for example the line that I have drawn below: However, ...
Nandula's user avatar
  • 783
0 votes
0 answers
16 views

Uploading mbtile to PostgreSQL via ogr2ogr results in tile boundaries

I have a large .mbtile file (all census tracts in the United States) that I would like to store in PostgreSQL. I am using ogr2ogr to upload the file to a table with the following command: ogr2ogr -f ...
Paige's user avatar
  • 1
0 votes
0 answers
23 views

Automatic link to database style in QGIS vanished

I have a couple of shapes in a PostgreSQL database link with QGIS. Once, I created specific styles for these layers and saved them in the PostgreSQL database as default style. Now, I loaded the ...
oppia04's user avatar
  • 735
1 vote
1 answer
71 views

OSM Vector tile ST_AsMVTGeom SQL query Coordinates error

I am currently working on developing a Node.js API with Express to retrieve vector tiles from a PostGIS database. I am using the ST_AsMVTGeom function to transform geometries into vector tiles, but I ...
AhmeX2's user avatar
  • 123
0 votes
1 answer
55 views

FME restart identity sequence when using Truncate Existing in the writer table handling drop-down menu

In FME 2023+, when using Truncate Existing in the Table Handling drop-down list in a PostgreSQL/PostGIS writer, is it possible to reset the numbering sequence associated to the primary key when this ...
swiss_knight's user avatar
  • 10.3k
0 votes
1 answer
33 views

FME discard destructive operations in the table handling drop-down list for PostgreSQL / PostGIS writers

In FME 2023, one can use a PostGIS writer to write some spatial data to a PostGIS database and that's great. In the table section, one can select one of several Feature Operations: When using upsert ...
swiss_knight's user avatar
  • 10.3k
0 votes
0 answers
19 views

Mergin db sync fails to start

I actually experiencing two type of issues. I am using DB-SYNC 2.1.1 on Windows. I have couple of tables in a database and I am actively using row level security, to modify the availability of data to ...
Tamas Kosa's user avatar
  • 1,507
1 vote
0 answers
113 views

Managing PostGIS many-to-many relationships between several tables with different geometry types from QGIS

I have this data set stored in a PostGIS (16+3.4) database: Each category of items being stored in its own table: CREATE TABLE public.rock ( id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, ...
swiss_knight's user avatar
  • 10.3k
3 votes
0 answers
54 views

PostGIS: Why does <-> and ST_Distance give two different results?

I have a query where I want to get the closes row by coordinate distance like so: SELECT A.id, st_distance(coords, ST_SetSRID(ST_MakePoint(-9.15054,54.115341), 4326), true) AS dst, A.coords &...
bcsta's user avatar
  • 191
1 vote
1 answer
49 views

Cannot cancel pg_routing query in local database

I have tried to cancel the hung query after a pg_routing query, but cannot. I have encountered this problem previously with pg_routing, but ended up really messing up the local database and had to ...
MJM's user avatar
  • 975
1 vote
0 answers
48 views

How to find the relation between track and track points after importing GPX data with ogr2ogr?

I've imported the gpx file into postgres db with postgis enabled using ogr2ogr: ogr2ogr -f "PostgreSQL" PG:"host=xxx user=xxx password=xxx dbname=xxx" "test.gpx" As ...
Mateusz Witkowski's user avatar
4 votes
1 answer
134 views

PostgreSQL PostGIS: get geography lines interceptions

I'm trying to solve one problem with PostGIS module in PostgreSQL 15 server. Incoming Data: Geographic Coordinates of Point 1. (X1,Y1) Geographic Coordinates of Point 2. (X2,Y2). Bearing angle from ...
Fes's user avatar
  • 41
1 vote
1 answer
76 views

Upgrading PostGIS 3.0 to PostGIS 3.3 on RHEL 8 fails

I am not a DBA. I am trying to update our PostGIS 3.0 package installation on a PostgreSQL 12 database to PostGIS 3.3 package on a RHEL 8 server. I am unable to install both packages at the same time ...
NC 2024's user avatar
  • 11
1 vote
2 answers
84 views

Find points x meters from a center in a specific direction (or line)

I just started to work with POSTGIS and I am currently working with signalised intersections. I have the center of the intersection as a point and the connecting roads as linestrings. I need to find ...
Rodrigo Sasse's user avatar
0 votes
0 answers
38 views

Moving tables with spatial data from SQL Server to PostGIS

I need to move tables with spatial data from SQL Server running on a server to PostGIS on another server daily. My current method is using geopandas to read the tables then writing the tables to ...
Sam's user avatar
  • 11
3 votes
1 answer
54 views

Joining two polygon tables based on conditions

I have two tables: table1 and table2, I would like to join polygons from table1 (purple) to table2 (yellow) if the polygons in table1 don't intersect or are coveredby table 2 polygons. I would like ...
MJM's user avatar
  • 975
1 vote
2 answers
90 views

Saving a QGIS project takes 10 minutes

I run a project with about 30 layers. Some of them contains ~1.000.000 objects stored in a PostGIS/PostgreSQL database Opening my project is fine. It takes only a few seconds But when I save the ...
plc's user avatar
  • 31
2 votes
1 answer
59 views

row_number modifying my code with a geometry update

I have a problem with my following view : CREATE OR REPLACE VIEW public.unites_foncieres_test AS SELECT row_number() OVER () AS ogc_fid, foo.code_insee, foo.nompro, foo.adr_prop, foo....
Jules Baraton's user avatar
2 votes
1 answer
114 views

Why can ogr2ogr upload data to a not previously existing table faster than to an existing empty one?

Recently I observed that it takes much less time for ogr2ogr to upload data to a PostGIS database if I do not create the destination table first. I would like to understand why this happens. Below is ...
zabop's user avatar
  • 2,214
0 votes
0 answers
101 views

QGIS WMS Server : "Opening of authentication db FAILED"

I am trying to setup a QGIS WMS Server and populate it with data from my PostgreSQL database but I keep getting CRITICAL Server[45873]: Error, Layer(s) ...
Nathan L.'s user avatar

1
2 3 4 5
84