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
1 vote
1 answer
45 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/...
1 vote
1 answer
693 views

postgresql statement taking ages to cancel in pgAdmin III

I was running a query in pgAdmin III and it was taking a long time. After 9 minutes I decided to cancel it using the stop button. The UI stated that it was cancelling and stayed in that state for ...
0 votes
1 answer
348 views

How to move a PostgresSQL/ PostGIS to another drive

Firstly I'd like to say I know little about databases (GIS background) and I might have bluffed it all so far. But we wanted to set up a database for use with QGIS. My understanding was that the ...
0 votes
3 answers
34 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 ...
0 votes
1 answer
525 views

KNN search two arrays with 2000 elements

What I did: installed postgresql 9.3.1 installed postgis 2.1.2 I tried to find the distance by selecting column input_variable_1 with value 50. I got the following result. select input_variable_1 &...
0 votes
1 answer
17 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 ...
0 votes
1 answer
344 views

Unable to import shapefile into PostGisDatabase - problem of connection

I have been trying to import/load a shapefile into a PostGis database without any success despite many trials and searching an answer in the documentation. Configuration I'm running Windows 64 bits ...
0 votes
1 answer
4k views

Are there limitations on using pg_dump on a materialized view such that the data won't be included in the results?

I have a database, call it maps, which contains 2 schemas, a & b. In each schema I have a number of tables: a.t1, a.t2, b.t1, b.t2 (plus others). The column sets on each of these tables is ...
4 votes
1 answer
764 views

Unable to send WHERE parameters to subquery

I have the following problem (in PostgreSQL in combination with PostGIS): I have the following nice query who clusters multiple records. Although it uses PostGIS function, my problem is not PostGIS ...
1 vote
1 answer
355 views

Howto do a hard upgrade from postgis-1.5 to postgis-2.0

I need to upgrade from postgis 1.5 to postgis 2.0 but i cant backup my data and restore it can someone give me a link where it is clear enough for doing this?
0 votes
1 answer
494 views

How to make ST_DWithin to use meters for distance in Postgis?

I have already asked a similar question here: Find the nearest geo points across two tables with Postgis (or without)? but now I am stuck around the correct geo-reference to calculate the distance ...
0 votes
2 answers
6k views

Psycopg2 Errors on SQL statement when trying to copy data from CSV file into PostgreSQL database

I am not a developer or PostgreSQL DB admin, so this may be basic questions. Logistics: Windows 10 server / pgAdmin 4 / Postgres 10 / Python 2.7.13 I'm using a python script to ingest external data, ...
7 votes
1 answer
4k views

&& vs ST_Intersects performance

I am trying to figure out points reside inside an rectangular area (envelope). I am having a little bit difficulty understanding performance implications of using && operator compared to ...
2 votes
0 answers
45 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'...
0 votes
2 answers
705 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
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 ...
2 votes
1 answer
505 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 ...
0 votes
1 answer
356 views

Why does PostGIS create an ugly table in my database called "spatial_ref_sys"?

I need to use PostGIS, and do use it, but it bothers me that it created this ugly "spatial_ref_sys" table in my database, in the main (public) schema, where I keep all my own tables. It looks ugly. ...
0 votes
1 answer
6k 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 ...
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 ...
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)? ...
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 ...
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 ...
0 votes
1 answer
385 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 ...
0 votes
1 answer
29 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 ...
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(...
1 vote
1 answer
254 views

PostgreSQL - PostGIS ST_SetValues - int array cast to double and back

I am using ST_SetValues with array defined as ARRAY[[9, 9], [9, 9]]::double precision[][]. If I store 32bit integer value in this array can I retrieve this value exactly after casting from double to ...
3 votes
1 answer
435 views

PostGIS - indexing - speed up query

I have this query (called from PHP, hence the $ variables): SELECT a.name, St_AsText(a.geom), b.name AS name_b, b.id FROM a LEFT JOIN b ON b.id = a.b_id WHERE UNACCENT(LOWER(a.name)...
3 votes
1 answer
199 views

Access points of geometric path data in PostgreSQL 8.1

Summary: I would like to perform basic operations on the points of geometric path data contained in a PostgreSQL 8.1 server which I do not have administrative access to. In particular I would like to ...
0 votes
1 answer
556 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 ...
0 votes
0 answers
331 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: ...
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 (...
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,...
0 votes
2 answers
266 views

Aggregating data by group and time gaps

I've got a postgresql+postgis database filled with GPS data: CREATE TABLE "my_table" ( "id" serial NOT NULL PRIMARY KEY, "gps_id" text NOT NULL, "lat" numeric NOT NULL, "lon" ...
1 vote
0 answers
436 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 ...
8 votes
2 answers
2k views

moving large PostgreSQL/PostGIS database

I need to move and upgrade a very large (~320 GB) PostGIS database from server1 (PostgreSQL 9.1, PostGIS 1.5) to server2 (PostgreSQL 9.3, PostGIS 2.1). The upgrade process is well-documented. The ...
-1 votes
1 answer
37 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 ...
2 votes
3 answers
590 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 ...
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 ...
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 ...
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" ...
0 votes
0 answers
233 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 ...
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 ...
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 ...
0 votes
1 answer
50 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 ...
0 votes
1 answer
256 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 ...
0 votes
0 answers
140 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 ...
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 ...
0 votes
0 answers
157 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 ...
1 vote
1 answer
63 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 ...

1
2 3 4 5 6