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
0 answers
11 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 ...
0 votes
0 answers
19 views

Postgis vs Postgres

I'm working on a legacy application and just saw its startup configuration has Postgres DB and URL is set with protocol postgis:// vs postgres://. Isn't postgis an extension under postgres? If so why ...
  • 101
0 votes
0 answers
13 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 ...
0 votes
0 answers
22 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" ...
  • 101
0 votes
0 answers
72 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 ...
0 votes
0 answers
25 views

Improve performance of postgis query with distinct

Hej, in my hobby project "luft.jetzt" I want to show the user the latest data from air monitoring stations around him. Due to my poor database skills, I am unable to write a performant ...
  • 1
2 votes
1 answer
145 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 ...
  • 300
1 vote
0 answers
46 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 ...
  • 203
0 votes
0 answers
25 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 ...
0 votes
1 answer
22 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 ...
  • 153
0 votes
1 answer
58 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 ...
  • 153
0 votes
0 answers
56 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 ...
  • 197
1 vote
0 answers
1k 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 ...
  • 11
0 votes
0 answers
40 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 ...
  • 113
1 vote
1 answer
31 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 ...
0 votes
0 answers
11 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 ((-...
0 votes
0 answers
36 views

Compare two tables - GPS points

postgresSQL 13.6 no query built yet, as this is a initial concept question. Two tables, each with a timestamp and lat and lon columns plus one ID column (not anywhere common between tables), ...
  • 25
0 votes
1 answer
63 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 ...
  • 25
-1 votes
1 answer
132 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....
1 vote
0 answers
116 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 ...
  • 111
2 votes
1 answer
9k 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 (...
0 votes
1 answer
193 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, ...
3 votes
1 answer
5k 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?
2 votes
2 answers
253 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)...
  • 31
0 votes
1 answer
369 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 ...
0 votes
1 answer
31 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....
  • 197
0 votes
1 answer
102 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(...
  • 125
3 votes
0 answers
171 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 ...
  • 141
0 votes
1 answer
836 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 ...
0 votes
1 answer
558 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 ...
  • 300
0 votes
1 answer
232 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....
  • 193
1 vote
1 answer
1k 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 ...
1 vote
1 answer
75 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....
2 votes
0 answers
57 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 (...
0 votes
1 answer
63 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 ...
  • 103
1 vote
2 answers
108 views

Are GIST Index point geometry useful to speed up spatial query?

I know the question is a bit generic. I have multiple tables with point geometries and a GIST Index eating many gigabits of space. From what I understrand about Gist Index on polygons or polylines, it ...
  • 31
0 votes
1 answer
157 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 ...
  • 101
0 votes
1 answer
212 views

pg_restore using PostGIS/PostgreSQL Dump Changes Data in Geography Column

Two PostGIS databases (PostGIS 3.0, PostgreSQL 13.1) were setup on two separate machines to be as close to each other as possible using Docker images. A dump of the database was taken from the first ...
  • 1,081
0 votes
1 answer
375 views

Creating a specific index tree structure from keys that have the tree-structure baked in? Indexing H3 Hexes

Preface: I am NOT a DBA, please be gentle on my terminology and explanations. I have data that already has a structure baked into it and need to create an index in my DB that represents the ...
0 votes
1 answer
1k views

How to fix double-encoded UTF8 characters in postgres

I have a dataset (shapefile) with the same problem as the post below: https://stackoverflow.com/questions/11436594/how-to-fix-double-encoded-utf8-characters-in-an-utf-8-table "A previous LOAD ...
  • 101
0 votes
1 answer
206 views

Geospatial join using ST_Contains or ST_Within seems slow, with costly query and bad estimate

Problem I have two tables, one which which includes a point column and the other which includes a boundary (table definitions below). I want to select rows from the first column where the point is ...
  • 101
1 vote
1 answer
276 views

What is the format of raw geometry column in PostGIS?

It doesn't seem to be a WKB, since it's basically a string, so what is it?
  • 149
0 votes
4 answers
56 views

Postgis reporting a point is 1216m away from itself using ST_Distance [closed]

I have a table containing a geometry column in 4236 projection. I take the ST_X and ST_Y values from one row, and create a point from them. I then transform the point and the original geometry column ...
  • 59
3 votes
0 answers
73 views

Postgis ST_Intersects behaves erratically

I am using Postgres 13.0 on a Mac, with Postgis 3.0. I encountered a problem where ST_Intersects produces the wrong result, and after narrowing it down, I arrived at the following: With the following ...
  • 31
0 votes
1 answer
114 views

cumulatively calculate the distance along a line

I am using Postgis to: calculate the cumulative distance in metres along the line store the cumulative distance in "m" I have a table with points on this line and each row holds lat and ...
  • 111
1 vote
0 answers
27 views

Expected function to return very few rows, but returns all rows

I am creating a Postgres/postgis function that is suppose to cut an input-geometry according to a 'hard-coded' table's geometry. The hard-coded table is called j.borders and its geometry attribute is ...
0 votes
1 answer
43 views

only use extended join-clause if some variable is TRUE [closed]

I am working with Postgis (extension to postgres) where there are several different geometry-types, i.e. 'polygons', 'points' 'linestrings', and multi-variants of these. I am trying to create a ...
1 vote
1 answer
433 views

PostgreSQL - trying to speed up query responses from view that has join and group by

I have these two tables: precipitaion: store precipitation data at daily (freq = 'daily') and hourly (freq = 'hourly') frequencies for 7 months so far (I receive regular updates). Column | ...
  • 111
0 votes
1 answer
4k views

remove postgres server from local machine

I know this is a real beginner question. But nevertheless I'm really confused:/ I wanted to have a clean installation of postgres on my ubuntu laptop. So I tried to delete everything related to ...
  • 101
0 votes
2 answers
3k views

Slow planning time on PostgreSQL 12.4

I have a database with Openstreetmap location data, and in particular there is a polygon table with around 500 million rows. I created indexes on all of my queries consisting of using gist(way) where ...

1
2 3 4 5 6