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
1 vote
1 answer
33 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
1 vote
0 answers
12 views

Confusing mixed geometry error in ST_Clip with raster and MultiPolygon

I'm getting an error about an operation on mixed geometries coming from a use of ST_Clip(raster, geometry). All the tables involved are uniformly SRID 4326 but maybe it's coming from an empty raster (...
user239945's user avatar
0 votes
1 answer
32 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.1k
0 votes
1 answer
27 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.1k
0 votes
1 answer
25 views

Possible QGIS issue suggesting that PostGIS ST_Colormap inserting NULLDATA values in output during converstion of greyscale to colour raster

I am trying to convert an BNUI 255 greyscale image, with values 10-80 into a colour version using the PostGIS extension(3.4) to Postgres(16.2) using the following code CREATE TABLE colour_ras AS ...
Dave's user avatar
  • 329
1 vote
2 answers
69 views

Filling field with values from field of another table using QGIS

I am using QGIS 3.28.9. I need to complete a field from a building's layer with the field's values from a plot's layer, based on the location of the buildings. At first I thought of doing it through ...
Mig TabSher's user avatar
2 votes
1 answer
65 views
+100

Finding interpolated value from raster data in PostGIS

I have a series of values that were computed for each cell on a grid on a given territory. Those values has been stored in a raster field in PostGIS (there are no missing data). I want to run a query ...
Thibault J's user avatar
0 votes
0 answers
13 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
0 votes
0 answers
28 views

Unable to obtain connection: Cannot create PoolableConnectionFactory (Connection to localhost:5432 refused)

I have a PostgreSQL server with PostGIS extension enabled running on docker with port 5432 exposed. I'm able to connect using psql -h localhost -p 5432 -U postgres -d db but when I try to use the same ...
Devarshi Goswami's user avatar
0 votes
0 answers
26 views

PostGIS install issues

I'm running Debian 12 and PostgreSQL 16 and trying to install PostGIS. At this point when I run apt-get install postgis I receive: Reading package lists... Done Building dependency tree... Done ...
WV_Mapper's user avatar
  • 161
1 vote
1 answer
95 views

Finding rectangular polygons in a multipolygon

I'm trying to do some analysis that requires finding rectangular gardens of a certain size. At present, I only have access to a data set of property extents with buildings punched out with the ...
CitizenFish's user avatar
1 vote
0 answers
102 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.1k
4 votes
1 answer
100 views

ogr2ogr: Unable to import shapefile with '-lco RFC7946=YES' parameter to the PostGIS db

I'm trying to convert multipolygon to in the right-hand format so that Leaflet can use it on the front end. ogr2ogr -nln [target_table] -nlt PROMOTE_TO_MULTI -lco GEOMETRY_NAME=geom -lco FID=gid -lco ...
matsukan's user avatar
2 votes
1 answer
87 views

Create a raw SQL statement ready to be used in PostGIS from a geometry in QGIS

Given an existing geometry in QGIS 3.28+ (e.g. from a temporary scratch layer), is there a quick way (i.e. with the lowest amount of user actions) to generate the PostGIS geometry definition (raw SQL ...
swiss_knight's user avatar
  • 10.1k
3 votes
1 answer
61 views

How to speed up ST_Union PostGIS query for complex problem

I'm fairly new to working with PostGIS and have experienced the issue of extremely long runtimes when working on my data. Here's what I'm trying to do: I have an infrastructure dataset that is made up ...
Felix_geospatial's user avatar
1 vote
0 answers
32 views

GeoServer feature type loading issue

I have a table on a PostGIS database with these fields: id [serial integer] pname [text] links [text] area [integer] docs [json] For this layer, I have created a WFS layer on GeoServer. However, the ...
ulrich's user avatar
  • 1,130
1 vote
1 answer
50 views

Why do I need to use quotation marks ("") to call a table in PostGIS? I remember not to need it in other exercises

When I try to run the following code I get an error that disappear when I call the tables using quotation marks. I remember having run tables before without it. select cine as INE from "...
user's user avatar
  • 153
2 votes
0 answers
47 views

Why is find_srid no longer immutable?

I recently upgraded our PostgreSQL engine from 11 to 13 for one of our dbs and along with that, I upgraded PostGIS from 2.4 to 3.4. We're using MapServer for one of our apps and it's generating ...
Rob Dennett's user avatar
3 votes
0 answers
50 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
0 answers
31 views

How to write GeoSPARQL query when hexadecimal representation of a binary encoded geometry

I am trying to write a GeoSPARQL using the geometry column of a layer imported from QGIS to PostGIS. The format of the geometry column is hexadecimal representation of a binary. When I try to use geo:...
BHH's user avatar
  • 21
1 vote
1 answer
65 views

Importing vector layers where geometry is WKT in PostGIS

I am trying to import the layers in QGIS to PostGIS. However, I want the geometry imported to be WKT and not WKB. How can I do that?
BHH's user avatar
  • 21
1 vote
1 answer
44 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
45 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
2 votes
1 answer
48 views

pgr_createTopology not working is dynamic sql code

I have the following dynamic sql query (left out many lines of code before this, but it works up to the pgr_createTopology element for creating the topology of an extracted stream network based on the ...
MJM's user avatar
  • 975
0 votes
0 answers
34 views

GPS points closest to checkpoints on the road [duplicate]

I'm a beginner with the following situation: There are fixed checkpoints on a road. The GPS of a vehicle traveling on the road registers timestamped points with a certain frequency, with a low level ...
TaoTao's user avatar
  • 131
1 vote
1 answer
90 views

What performance recommendations are there for QGIS/PostGIS/PostgreSQL implementations -- Settings and configuration [closed]

Intro: Our organisation uses QGIS Desktop and PostgreSQL/PostGIS for spatial data storage and management. We've recently reviewed postgresql.conf settings with a PostgreSQL database specialist but ...
GIS_py's user avatar
  • 483
4 votes
1 answer
129 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
3 votes
1 answer
138 views

Calculate maximum inscribed rectangle in a polygon

I saw this amazing answer here, but am struggling to run the function. When I feed it a geometry it returns NULL values for each row of the input dataset. Any ideas what might be causing this? I'm new ...
Jonathan Nolan's user avatar
4 votes
0 answers
47 views

MapInfo to PostGIS - QGIS styles

i just discovered the possibilites and advantages of holding my data in a PostGIS database. As we work with a mix of MapInfo, QGIS (Lizmap and QField) I'm looking for a convenient way to save my data ...
sn1ks's user avatar
  • 2,962
1 vote
1 answer
40 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
75 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
33 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
48 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
2 votes
0 answers
47 views

PostGIS ST_GeneratePoints returns empty coordinates

I'm trying to generate test data using some of the built-in functions in PostGIS (Postgres 15.5, PostGIS 3.4.1). One of the things I need to do is generate a bunch of points within a general area. I'm ...
ebbishop's user avatar
  • 121
1 vote
2 answers
88 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
0 answers
57 views

PostGIS slow performances on small table and basic SELECT query

A few weeks ago, I received a query to install a PostGIS database for a few of my users. I'm not familiar with this but yet installed quite simply the extension using the sudo apt install postgresql-...
WebManiaK's user avatar
2 votes
0 answers
54 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
94 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
  • 1,812
2 votes
2 answers
53 views

How to inverse spatial query [duplicate]

I have two tables with polygons (CTR_daim and CTA_LOW_1111). I have this query to select all polygons from CTR_daim that is intersected by any of the polygons from table CTA_LOW_1111 WITH ...
Paul's user avatar
  • 477
4 votes
1 answer
48 views

ST_CLIP with one raster and one polygon

I don't understand how I can select a raster and polygon by ID and then clipping them. Something like: SELECT ST_CLIP(selected_raser.rast, 1, selected_polygon.geometry) From raster WHERE ID = 99 ...
Squeezie's user avatar
  • 240
3 votes
0 answers
75 views

FME PostGIS reader results in a data set with no geometries

Using FME(R) 2023.0.0.1 [build:23288] (the same behaviour occurs on 2023.1.1.1 [build:23636]) at the time of writing, I'm facing a severe bug(?) with a PostGIS reader. Indeed, when connecting to a ...
swiss_knight's user avatar
  • 10.1k
2 votes
0 answers
45 views

Where PostGIS gets its spatial_ref_sys table data

After updating PostGIS versions we noticed there were thousands of extra projections added that did not exist previously. I was wondering where PostGIS gets its list of projections, and how is it ...
William S's user avatar
3 votes
0 answers
51 views

How to close a GDAL PostGIS connection?

This issue has been bugging me for years, desperation got the better of me so now I'm here to seek the help of the public. I'm storing raster data in a PostGIS database, and I'm using GDAL from Python ...
Jeroen's user avatar
  • 31
2 votes
0 answers
60 views

Create bounding box polygon from extent of point features in PostgreSQL

I have a linestring table (table a) and a point table (table b). What I want is to extract the start points from the features in the linestring table and union them with the point features in the ...
kwiwe's user avatar
  • 409
2 votes
1 answer
54 views

Backup table does not replicate geometry, fed by trigger in pgAdmin (postGIS)

I have two tables, A and B. B is a backup of A. Table A has a trigger: CREATE OR REPLACE TRIGGER trigger AFTER INSERT OR UPDATE OR DELETE ON A FOR EACH ROW EXECUTE FUNCTION ...
Tobias Holms's user avatar
1 vote
0 answers
68 views

Road data merge weather condition

I have a shapefile that shows fog, wind and precipitation values ​​between 1 and 5. Additionally, this file consists of grids. My goal is to segment the OSM road data that fall into these grids in ...
MGamze's user avatar
  • 11
5 votes
2 answers
174 views

Use QGIS variables in PostGIS query

How can I build dynamic query to database? In PostGIS database I have table with a column "column3" in which there are values project1, project2, project3, ... project23, project24. I have ...
mapdraftsman's user avatar
2 votes
0 answers
62 views

How to return array or JSON object using function ST_AsMVT in PostGIS

I am working in PostgreSQL/PostGIS and could you please explain me how I can return an array or json object data in the tiles using ST_AsMVT function. Right now I am using GeoJSON to return an array ...
JuniorLittle's user avatar
0 votes
1 answer
46 views

Maven Dependency Resolution Issue for GeoServer GS:WPS

I am encountering an issue while building a GeoServer wps using Maven, and I could use some guidance. Error Description: I am attempting to build a GeoServer wps using Maven (mvn clean install). ...
Niko's user avatar
  • 1
2 votes
1 answer
53 views

ST_Contains Removes Invalid Geometry & ST_MakeValid PostGIS Query Gives Error

I'm trying to query a list of polygons (geo_parcels) and get all of them, and also spatially add any points or polygons (geo_buildings) that are in or on that polygon. Normally I'd use a query like ...
Andrew T's user avatar
  • 243

1
2 3 4 5
188