Skip to main content

Questions tagged [postgis]

PostGIS is a database extension for PostgreSQL providing additional spatial types, indexes, functions, and aggregates.

Filter by
Sorted by
Tagged with
0 votes
3 answers
39 views

Why is the Postgres query planner choosing the wrong strategy?

I have a Postgres/Postgis table with ~50M rows. I am making requests similar to this: select * from building b where ST_Intersects(b.point, some_fixed_geometry) order by id limit 20 offset X; The ...
Istopopoki's user avatar
0 votes
1 answer
18 views

Detect concurent objects based on category and timespan

I have a Postgres table with categorys and timespans (columns valid_from and valid_to). This represents road sign that are installed for certain time span. I want to compute how many road signs there ...
Mario's user avatar
  • 101
1 vote
1 answer
50 views

Inaccurate estimates for geospatial types

This is my first dabble with Postgres and geospatial. Looking at a slow query which includes ST_Intersects(a.geography, b.geography). The optimiser estimates are 2500x out. https://explain.dalibo.com/...
Mark Storey-Smith's user avatar
2 votes
0 answers
46 views

Upgrade and migration of PostgreSQL 10 and PostGIS 2.4 to PostgreSQL 16

I have a really old PostgreSQL and PostGIS bundle (10 and 2.4), that I want to upgrade and migrate to a different server. Upgrading postgres alone wouldn't be a problem, but PostGIS makes it tricky. I'...
Dreamscape's user avatar
0 votes
0 answers
30 views

Does ST_AsMVTGeom/ST_AsMVT truncate data, or is my query wrong?

Here's the individual activity, which is GeoJSON displayed on the map rather than doing any ST_AsMVTGeom/ST_AsMVT work: Here's the result of the ST_AsMVTGeom/ST_AsMVT query: If you compare the ...
James Chevalier's user avatar
0 votes
1 answer
30 views

Automating PostgreSQL query so I don't have to change schema name each time in sql query

I have a multistep PostgreSQL + PostGIS query/geoprocessing operation that I need to change the schema name for the output tables each time I run it. The base tables I use are all in another schema ...
MJM's user avatar
  • 127
0 votes
1 answer
17 views

postgis uprade route

I need to upgrade source: rhel7-pg-10-postgis-2.4 target: rhel8-pg-14-postgis-3.1 is my assupmtion correct, that I need to go in these steps (using pg_dump/pg_restore to jump between versions)? ...
vrms's user avatar
  • 195
0 votes
1 answer
59 views

Split lines against polygons in a PostGIS trigger

I have a table of lines, named segment, stored in the reference schema, and a table of polygones named communes in a donnees_externes schema. I try to create a trigger that split the features of ...
idrizza's user avatar
0 votes
0 answers
13 views

st_linemerge unless property changes

My question is as follows: Given a LineShape as below, merge all elements UNLESS: Node of degree 3 or higher is present maxspeedkph property is different (50kmph -> 30kmph for example) however ...
Alex Vermeulen's user avatar
0 votes
1 answer
30 views

Recommendation on storing 3D flight data in PostgreSQL. Use POINTZM?

I am looking to store latitude, longitude (both decimal) and altitude (meters) for use with flight data in PostgreSQL 15. I have decided on using the POINT data type in PostgreSQL for latitude and ...
Justin's user avatar
  • 439
1 vote
0 answers
30 views

ST_SnaptoGrid not working properly

I want to perform ST_SnaptoGrid on a line but the result is very strange. I tested this line of code on 3 separate postgis sessions and the results of all of them was the same: SELECT ST_AsText(...
Sajjad's user avatar
  • 13
0 votes
1 answer
560 views

Making use of ENUM in CONSTRAINT

I am creating a PostGIS database in which a lot of schemas should have the same constraints. Meaning in the future, similar tables should have the same limitations of values entered. My constraint ...
KoGIS's user avatar
  • 3
0 votes
0 answers
337 views

PostGIS in docker file FATAL no pg_hba.conf entry for host

I have a docker-compose.yml file with two image (Postgres extended PostGIS and pgadmin4): version: "3.8" services: postgis: image: postgis/postgis:15-3.3 container_name: ...
Gianni Spear's user avatar
0 votes
1 answer
92 views

pg_dump unable to produce a valid backup

I have a table in postgis as follows: CREATE TABLE public.a_meter ( smid integer NOT NULL, smgeometry public.geometry(Point,32640) NOT NULL, install_node_code text, "ID" text,...
Sajjad's user avatar
  • 13
1 vote
0 answers
438 views

Unable to Install PostGIS Extension for PostgreSQL 15 on Mac Book with M1 Chip and macOS Ventura 13.3.1

Issue: Unable to install PostGIS extension for PostgreSQL 15 on Mac Book with M1 chip and macOS Ventura 13.3.1. Steps I have taken: Installed PostgreSQL 15 using the terminal. Installed PostGIS using ...
Ravers UA's user avatar
-1 votes
1 answer
39 views

Postgres linking two tables

i am trying to set up a geo data base for my company using PostgreSQL with pgAdmin4. I am very new in database administration. We need a database storing the information about drilling points. Each ...
Anna's user avatar
  • 1
0 votes
0 answers
69 views

We have lost permissions on the topology scheme

we have a postgresql database with postgis, with the following versions: PostgreSQL 10.21 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit ...
Antonio Gamiz Delgado's user avatar
0 votes
0 answers
14 views

GIS database table setup with multiple columns or FK

I am trying to design a environmental GIS table setup in a geospatial database. The workflow process is to create a Permit To Disturb (PTD Table) and for each PTD there will be a point, line and ...
Christian Leidersdorff's user avatar
0 votes
0 answers
39 views

How to make this location-based complex query more efficient?

I'm building a dating app, and it needs to filter based on several conditions: Distance Gender and Orientation Liked or unliked status I'm using Postgis extension. In this case "my own" ...
Sallar's user avatar
  • 101
0 votes
0 answers
235 views

Why is the query time of my PostgreSQL DB so much slower than in my MySQL DB?

I've been testing the performance of my PGSQL DB and MySQL DB with around a million entries and I've come to the conclusion that MySQL seems to execute my queries much faster, but I feel it should be ...
Romek's user avatar
  • 109
2 votes
1 answer
508 views

Installing PostGIS SFCGAL 1.4 extension in the latest official PostGIS docker image (Debian bullseye based)

I do need to use the PostGIS SFCGAL extension in its latest version (i.e. 1.4.1 at the time of writing). This extension is based (and needs) the libsfcgal system library. Because the official PostGIS ...
s.k's user avatar
  • 372
1 vote
0 answers
101 views

Curious results from pg_stat_user_indexes

PostgreSQL 12.8, PostGIS 3.1.4 Running select * from pg_stat_user_indexes, there are some rows that have 0 for idx_scan, and a >0 result for idx_tup_read. The indexes that match this condition are ...
Avocado's user avatar
  • 223
0 votes
0 answers
26 views

Why is GIS WHERE clause costing me so much?

I've got this view that has problems with performance, at the moment I use st_intersects to filter this data, something that's a necessary part of the functionality, I need to be able to see that a ...
Hannes Colt's user avatar
0 votes
1 answer
51 views

some questions on building PostGIS from Source

From the PostGIS manual about Compiling and Install from source I get the general procedure to be ... Install postgres get a couple of libraries PostGIS depends on download and extract the PostGIS ...
vrms's user avatar
  • 195
0 votes
1 answer
257 views

migrating databases from postgres-11-PostGIS-2.5 > postgres-12-PostGIS-3.2.2

In my current example I have to migrate ~90 databases between 2 Servers ... source: debian11, postgres-11, postGIS 2.5 target: rhel8, postgres-12, postGIS 3.2.2 on PostGIS upgrades i read in the ...
vrms's user avatar
  • 195
0 votes
0 answers
141 views

How to prevent user to delete more than 2 rows at the same time

QGIS 3.22 & PostgreSQL 14. Many colleagues insert features in a Postgres/PostGIS database using QGIS and import/export to PostgreSQL database plugin. Among many parameters, there is add to or ...
Leehan's user avatar
  • 207
1 vote
0 answers
6k views

How to install postgis on Postgres 15?

i do not find a postgis package for postgres 15 on ubuntu. There are tons of blog posts out there showing all the same line: sudo apt install postgis postgresql-x-postgis-3 x stand for the postgresql ...
Richard's user avatar
  • 11
0 votes
0 answers
158 views

how to install the postgis extension .... despite everything

here is my problem: I use a PC (with "user" rights) on which the postgresql software is installed (without postgis). I can't download postgis via stackbuilder (port 443 closed) and I can't ...
fcka's user avatar
  • 125
1 vote
1 answer
64 views

Order a row before/after another based on value in the previous row

I've been trying to figure out a way to order a row after another based on the value of a column from the previous row. In my SQL query below, I first initially want to order the dataset by distance ...
ordane87's user avatar
0 votes
0 answers
14 views

Same Spatial Query on nodes table gives different read time

I have created 2 databases. First one has PGSnapshot Schema which looks like this. On this I fire query select id,ST_AsText(geom) from nodes where ST_Intersects(geom , ST_GeomFromText('POLYGON ((-...
Yasha Jadwani's user avatar
0 votes
1 answer
128 views

Add row from table B based on minimum distance between table A and B

I am a beginner with PostgreSQL and have two tables A and B, both with multiple columns, including a point column (geom): TableA includes a given 'ID', geom, etc , TableB is a "DB" of ...
domiho1's user avatar
  • 25
0 votes
1 answer
804 views

Add value from another table by comparing tables columns [closed]

I have 2 tables in postgres like: table A: unique_coords unique_id a 1 b 2 c 3 d 4 . . table B like: coords id a a c d c c d c I want to fill the id column in table B based on table A....
Fardin Esmaeili's user avatar
1 vote
0 answers
243 views

Spatial database architecture with Apache Parquet, PostgreSQL, and PostGIS on on-premises bare-metal S3/MinIO cluster

Designing storage architecture for Petabyte-scale geospatial data; starting from scratch. Creating a MinIo cluster to store the objects in S3 buckets. To store the metadata, I’m considering the Apache ...
jpinelo's user avatar
  • 111
5 votes
1 answer
26k views

SQLSTATE[08006] [7] FATAL: the database system is in recovery mode

I have some problem with PostgreSQL and PostGIS. I received an error after running the below query with some coordinates on special raster file. It worked fine until last week. The query is: select (...
mohammad karimi's user avatar
0 votes
1 answer
380 views

I have a GIST index and a regular index, one intended for filtering, one intended for sorting, but Postgres WILL NOT use both

I have a table with three columns: two can be used to filter the table, and the last can be used to sort it: create table thing( id serial primary key, location geometry(Point, 4326) not null, ...
Cooper Wolfe's user avatar
4 votes
1 answer
13k views

ERROR: could not open extension control file "/usr/share/postgresql/13/extension/postgis.control": No such file or directory

Ubuntu 21.10 , PostgreSQL 13. I catch error ERROR: could not open extension control file "/usr/share/postgresql/13/extension/postgis.control": No such file or directory how to fix it?
Vy Do's user avatar
  • 209
2 votes
2 answers
316 views

improve query performance in postgres

i have a table addressfabric_v2_2021_12 CREATE TABLE IF NOT EXISTS public.addressfabric_v2_2021_12( country character varying(10) COLLATE pg_catalog."default", areaname1 character varying(10)...
sunny's user avatar
  • 31
1 vote
1 answer
834 views

Postgres generated column silently failing

I am recreating a table which used to be updated by triggers, to the new generated column types. I'm running into a problem where if the generated column can be NULL, they silently fail and get ...
Kevin Potgieter's user avatar
0 votes
1 answer
45 views

PostGIS Error : size of index too small

PostgreSQL 13.5, PostGIS 3.2, Windows Server 2012. My main goal is to create an unique index on a column containing geometries. Here is the table : CREATE TABLE IF NOT EXISTS geometrie....
Leehan's user avatar
  • 207
0 votes
1 answer
210 views

Pass cast type arguments to procedure with execute format

I need to create multiple views on geospatial data each explicitly casting to the right geometry & I would like to create a helper procedure to do so like this: CREATE PROCEDURE create_view_events(...
eliangius's user avatar
  • 145
3 votes
0 answers
256 views

Nested cursor under update command [closed]

I am building a procedure to label database rows if certain records meet some conditions. I am looping over rows using cursor and for every row I check for some conditions, if conditions are met I ...
DrJacoby's user avatar
  • 141
0 votes
1 answer
1k views

Just delete 50.000.000 of rows on a PostgreSQL table and DB still very slow

sorry for my poor english. I have a postgres DB runing on amazon RDS (db.t3.small), with django as a backend. i have made a mistake and created 50.000.000 rows. when i figure out (because queries on ...
Nahuel Fernandez's user avatar
0 votes
1 answer
1k views

Postgis extension doesn't seem to be taken into account when restoring a PostGIS database: ERROR: type "public.geometry" does not exist

I am struggling trying to restore a PostgreSQL dump that has been made using pg_dump (v. 12+) out of a production database having PostGIS installed, the very first issue is this one when restoring the ...
s.k's user avatar
  • 372
0 votes
1 answer
368 views

Using subquery in WHERE makes query extremely slow

I have this rather basic query that is very slow for reasons I can't figure out: SELECT s.id FROM segments s WHERE ST_DWithin( s.geom::GEOGRAPHY, ST_Envelope((SELECT ST_COLLECT(s2....
Jukurrpa's user avatar
  • 193
1 vote
1 answer
3k views

Using parameters in stored procedure with plpgsql

I am trying to return the count of overlapping polygons. The problem is that it complains about my "$1" signs: Error is "SQL state: 42601", syntax error at "$1". Why ...
Teh Swish's user avatar
1 vote
1 answer
93 views

Query slowing down the database performance

I have a query to find the maximum spacing between charging stations on any given route, using PostGIS and pgRouting. An example query is below: select (max(delr) * st_length(line::geography) * 0....
Chintan Pathak's user avatar
2 votes
0 answers
66 views

PostgreSQL: issue with query plan

I am trying to speed up a search query. The context is the one of Sentinel 2 products (satellite images). Trying to fasten the spatial intersection, I have a table containing all MGRS tile geometries (...
Thomas Coquet's user avatar
0 votes
1 answer
81 views

SQL query: insert values into polygon where features of the polygon intersects another polygon [closed]

I'm very new to sql so I apologize in advance for my very dummy question... I'm looking for a way to insert into a table A some values stored in another table B only for the features of table A that ...
Linda's user avatar
  • 103
2 votes
3 answers
601 views

Is a GIST index on a geometry point useful to speed up a spatial query?

I have multiple tables with point geometries and a GiST index eating many gigabits of space. From what I understand about GiST indexes on polygons or polylines, it will store the bounding box and ...
Boodoo's user avatar
  • 45
0 votes
1 answer
271 views

Does daily pg_dump mess up postgres cache?

I migrated my geospatial Postgres 12.5 database to another cloud provider. I use postgis and I have around 35GB of data and 8GB of memory. Performances are way worse than on my previous provider, and ...
Pak's user avatar
  • 101

1
2 3 4 5 6