Questions tagged [postgis]
PostGIS is a database extension for PostgreSQL providing additional spatial types, indexes, functions, and aggregates.
270
questions
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 ...
- 9
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 ...
- 11
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 ...
- 103
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 ...
- 11
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....
- 133
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 ...
- 131
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