Questions tagged [postgis]
PostGIS is a database extension for PostgreSQL providing additional spatial types, indexes, functions, and aggregates.
250
questions
3
votes
0answers
35 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 ...
0
votes
1answer
45 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
0answers
66 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: ...
0
votes
1answer
48 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 ...
0
votes
1answer
60 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....
1
vote
1answer
57 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
1answer
41 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
0answers
41 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
1answer
25 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 ...
0
votes
0answers
24 views
PostgreSQL + Postgis strange execution plan
I have constructed the following query to find the % area of intersection between 2 geometry columns then I join this to another table. This query is extracted from a larger query.
SELECT fractions....
1
vote
2answers
37 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 ...
0
votes
1answer
52 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 ...
0
votes
1answer
27 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
0answers
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 ...
0
votes
0answers
14 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)::...
0
votes
1answer
162 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 ...
0
votes
1answer
57 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 ...
1
vote
1answer
63 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?
0
votes
4answers
45 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 ...
0
votes
0answers
7 views
How to manage datas from specific world areas (i.e. several CRS) in Postgis?
Until now, I used to store spatial data concerning the French metropolitan territory in a PostGis database. The reference coordinate system chosen is 2154 RGF93/Lambert93.
Very soon, I will have to ...
3
votes
0answers
55 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 ...
0
votes
1answer
54 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 ...
1
vote
0answers
21 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
1answer
34 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
1answer
42 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 | ...
0
votes
1answer
544 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
2answers
1k 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 ...
0
votes
1answer
114 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 ...
2
votes
1answer
27 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 ...
0
votes
0answers
396 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 ...
0
votes
2answers
175 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'
...
1
vote
1answer
46 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 ...
0
votes
0answers
58 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 ...
2
votes
1answer
56 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))
...
0
votes
0answers
20 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 ...
0
votes
1answer
63 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 ...
0
votes
1answer
91 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" ...
0
votes
1answer
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 ...
0
votes
1answer
549 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.
1
vote
0answers
103 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 (...
1
vote
1answer
39 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 ...
0
votes
1answer
36 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 |
+----+------+----...
0
votes
1answer
40 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
1answer
1k 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 ...
0
votes
1answer
28 views
group several columns together in materialized view
For statistical reasons, I need to group several ids in a single column of a materialized view.
This should form a list of ids from other tables with an info. Further processing will be done on this ...
0
votes
1answer
19 views
Restrict access to my PostgresSQL/ Post GIS database used through QGIS
We have a small network of users accessing our QGIS data stored on a PostGIS database (in 3 schemas) which sits on my local drive in my computer. This drive is then shared with the others. Due to the ...
0
votes
2answers
151 views
How to use pg_restore in database with different postgis installation (postgis schema location)?
I have database1 where postgis is installed in the public schema.
And database2 where postgis is located in a schema called postgis.
When I dump database1.schema1, schema1.table1 references its geom ...
0
votes
3answers
23 views
query two point values from a table then insert a new record after creating a line from the two points
I am running postgres: 9.5.19
I have already point values as separate records in a table. I want to create a new record which represents a line geom derived from the two points above. I have lots of ...
0
votes
1answer
33 views
Postgres/PostGIS is returning data from a table that doesn't exist (I'm a newbie -- show me why I am wrong)
I am following a tutorial for PostGIS. I have uploaded all the example datasets (all shapefiles) into a PostgreSQL database, although I have modified the names so they do not all match the names of ...
0
votes
1answer
88 views
Multiple versions of PostGIS and PgRouting on CentOS 7
I'm trying to install multiple versions of PostGis and PgRouting in the same instance of PostgreSQL v9.6, using CentOS 7.5.
I've been fighting with dependencies and shared libraries, to make all ...
