Questions tagged [postgis]
PostGIS is a database extension for PostgreSQL providing additional spatial types, indexes, functions, and aggregates.
285 questions
0
votes
1
answer
22
views
change ownership of output file when run as postgres user or redirect?
I am making a query as user postgres via my current user, xyz. I am returning the results of this query as JSON. I want to save this (huge) JSON into a file owned by my current user xyz. And I do not ...
- 103
0
votes
1
answer
79
views
How to migrate a PostgreSQL database with the postgis schema?
I've been tasked to migrate a bunch of postgres databases from version 10.5 to 16. Most contain the PostGIS extension (ver. 2.4.4), I've successfully migrated a few via the postgis_restore.pl script. ...
- 694
0
votes
1
answer
123
views
Microsoft asking me to remove Postgis to migrate database, any risk assoacited?
We are migrating our Postgresql Single Server to Flexible Server using Azure Migration and after a series of errors, the support engineer is telling me to remove the postgis extension for the ...
- 121
0
votes
3
answers
315
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 ...
- 121
1
vote
1
answer
104
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/...
- 31.9k
1
vote
1
answer
64
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 ...
- 111
0
votes
1
answer
34
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)?
...
- 293
0
votes
1
answer
70
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 ...
- 449
0
votes
1
answer
1k
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 ...
- 3
0
votes
1
answer
128
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,...
- 13
-1
votes
1
answer
71
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 ...
- 1
0
votes
0
answers
23
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
296
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 ...
- 109
1
vote
0
answers
158
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 ...
- 255
0
votes
1
answer
100
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 ...
- 293
0
votes
0
answers
189
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 ...
- 205
1
vote
1
answer
101
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
1
answer
194
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
vote
0
answers
313
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
0
votes
1
answer
609
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,
...
2
votes
2
answers
356
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
77
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....
- 205
3
votes
0
answers
311
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
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 ...
- 434
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 is ...
- 11