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
0 answers
17 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 ...
user avatar
  • 111
0 votes
0 answers
151 views

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

I have some problem with postgresql and postgis. i got error on below query with some coordinates on special raster file. notice that its worked until last week. the error is : SQLSTATE[08006] [7] ...
user avatar
0 votes
1 answer
40 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, ...
user avatar
0 votes
1 answer
602 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?
user avatar
0 votes
0 answers
51 views

MongoDB vs PostGIS for geospatial data

My main concern is that I was I/O and bounding circle queries to be lightning fast. Does MongoDB spatial offer any advantages over POSTgis?
user avatar
2 votes
2 answers
196 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)...
user avatar
  • 31
0 votes
1 answer
56 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 ...
user avatar
0 votes
1 answer
23 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....
user avatar
  • 167
0 votes
1 answer
18 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(...
user avatar
  • 113
0 votes
0 answers
19 views

How to generate hexagon polygons with coordinates of the center point in PostGIS?

I have given a database with coordinates and want to use these coordinates as the center points for hexagon polygons. PostGIS provides this convenient function: ST_Hexagon The example from the ...
user avatar
  • 1
3 votes
0 answers
53 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 ...
user avatar
  • 141
0 votes
1 answer
170 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 ...
user avatar
0 votes
0 answers
296 views

Restoring a dump of a schema (made with the -n option of pg_dump) of a postgis database; schema "public" already exists

While trying to restore a PostgreSQL dump that has been made using the -n public option of pg_dump (see: Postgis extension doesn't seem to be taken into account when restoring a PostGIS database: ...
user avatar
  • 216
0 votes
1 answer
145 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 ...
user avatar
  • 216
0 votes
1 answer
82 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....
user avatar
  • 193
1 vote
1 answer
348 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 ...
user avatar
1 vote
1 answer
53 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....
user avatar
2 votes
0 answers
50 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 (...
user avatar
0 votes
1 answer
37 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 ...
user avatar
  • 103
1 vote
2 answers
57 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 ...
user avatar
  • 31
0 votes
1 answer
88 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 ...
user avatar
  • 101
0 votes
1 answer
96 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 ...
user avatar
  • 1,001
0 votes
0 answers
27 views

Order of tables affects the use of index

I'm trying to optimize a large set of queries, and I don't understand why the order of tables in the WHERE clause makes such a big difference (3x). I thought that the query planner didn't care about ...
user avatar
  • 123
0 votes
0 answers
27 views

fetch elements between bounds in postgis

I have a table 'locations' in postgresql: name (character varying(24)) geography (geography) I insert data like this: INSERT INTO locations (name, geography) VALUES ($1, ST.MakePoint($2,$3)::...
user avatar
  • 121
0 votes
1 answer
547 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 ...
user avatar
  • 101
0 votes
1 answer
101 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 ...
user avatar
  • 101
1 vote
1 answer
133 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?
user avatar
  • 139
0 votes
4 answers
54 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 ...
user avatar
  • 59
3 votes
0 answers
67 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 ...
user avatar
  • 31
0 votes
1 answer
82 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 ...
user avatar
  • 111
1 vote
0 answers
24 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 ...
user avatar
0 votes
1 answer
41 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 ...
user avatar
1 vote
1 answer
88 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 | ...
user avatar
  • 111
0 votes
1 answer
2k 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 ...
user avatar
  • 101
0 votes
2 answers
2k 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 ...
user avatar
0 votes
1 answer
170 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 ...
user avatar
2 votes
1 answer
35 views

Is there a reason I should not set my default data location to google drive when setting up PostgreSQL?

I'm brand new to working with databases and am setting up a PostgreSQL database which will be used for personal projects. I'm setting it up with the postgis extension and will use both for some basic ...
user avatar
0 votes
0 answers
513 views

Installation of PostGIS extension not working on a docker image (seems not found by further commands)

Context I have a PostgreSQL code for installing the PostGIS extension which is working great on my machine but which doesn't work on a docker image. I cannot understand why. Here are the ...
user avatar
  • 216
0 votes
2 answers
395 views

How to get the type of geometry column in postgres?

I need to determine the type of geometry column and doing it with the following query: SELECT type FROM geometry_columns WHERE f_table_schema = 'public' AND f_table_name = 'table_name' ...
user avatar
  • 139
1 vote
1 answer
110 views

Create 7 day rolling average in PostGIS for each feature

I have a timeseries dataset that looks like this: uid | geom | date | count | ------------------------------------- 1 | FeatureA | 2016-02-01 | 1 | 2 | FeatureA | 2016-02-02 | 2 ...
user avatar
0 votes
0 answers
110 views

Can't get PostGIS to show points on map - no button to activate map?

PostgreSQL 12 and PostGIS 3.0.1 on Windows 10, using PGAdmin4. I'm totally new to PGAdmin4 having recently come from PostgreSQL 9 / PGAdmin III - frankly, I don't like it but I'm trying. For the ...
user avatar
2 votes
1 answer
77 views

Best indexing strategy for spatial join and ranged lookup

Context We have two tables (not the real tables used in the examples below. They are from a toy DB im using for testing): Incidents_2 (Columns of interest are a geom, and a reported_at (int8)) ...
user avatar
0 votes
0 answers
34 views

PostGIS buffer in miles not lat/long degrees

I am trying to take two points, add a buffer around them (in meters), draw a line between them, draw a buffer around that, and then query whether items are inside the area of the circles expanded line ...
user avatar
0 votes
1 answer
68 views

PostGIS trigger is fired without error, but the action seems not executed

Context I have set up a trigger on a PostgreSQL (+Postgis) table containing observation points. This trigger must compute a new geometry for each inserted or updated point. This new geometry must be ...
user avatar
  • 216
0 votes
1 answer
132 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" ...
user avatar
  • 145
0 votes
1 answer
1k views

'ERROR: could not access file "$libdir/postgis-2.5": No such file or directory' [closed]

Dealing with PostGIS has been a nightmare from day one. Every single time I have to touch it in any way, or update PostgreSQL, it turns into a broken mess. The only reason I use it is because I need ...
user avatar
1 vote
1 answer
887 views

How do I check if PostGIS is installed in my PostgreSQL?

I'm trying to check whether or not the PostGIS extension is installed, as a PG query. I'm unable to find this info out.
user avatar
1 vote
0 answers
117 views

Installing Postgis and PGRouting via pgenv

We have a product where our production environment uses Docker and has no issue with installing things like postgis and pgrouting. However, we have multiple remote developers and many of them (...
user avatar
1 vote
1 answer
58 views

Would it be worthwhile to use the PostGIS extension for custom planets?

I need to be able to store latitude and longitude in several different tables. My understanding is that PostGIS has a geography column type that does this. However, I'm not storing coordinates on ...
user avatar
  • 111
0 votes
1 answer
38 views

Query table based on column value

I've those tables on my PostGIS db. poi_list +----+------+-----------------+---------------+--------------------+ | id | nr | source_layer | source_column | source_id | +----+------+----...
user avatar

1
2 3 4 5 6