Questions tagged [postgis]
PostGIS is a database extension for PostgreSQL providing additional spatial types, indexes, functions, and aggregates.
229
questions
1
vote
1answer
34 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
15 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
71 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
36 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
25 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
61 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
0answers
9 views
Losing connection to the server when using PG Routing function pgr_alphaShapes
I am running PG Routing v. 3.0 on Postgresql 12.2 and the pgr_alphaShapes function is giving me a lot of trouble. Following the documentation here, I passed a geometry and a spoon radius.
SELECT *
...
0
votes
2answers
23 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
28 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
18 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 ...
0
votes
0answers
7 views
Library that can generate random geospatial data in GeoJSON
Is there a library that can help to quickly generate random geospatial features in GeoJSON format (preferably, but can be anything), based on the parameters that we pass in. For example - generate ...
2
votes
1answer
47 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
16 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
51 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
24 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
377 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
73 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
78 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
19 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
31 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
0answers
39 views
'pg_query_params(): Query failed: ERROR: relation “postgis.spatial_ref_sys” does not exist'?
QUERY: SELECT proj4text FROM PostGIS.spatial_ref_sys WHERE srid = 4326 LIMIT 1
I have no such query anywhere in my code. 100% of all my references to PostGIS are done like:
SELECT whatever FROM "...
0
votes
1answer
18 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
118 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
21 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
14 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
50 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
19 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
30 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
55 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 ...
0
votes
1answer
16 views
Store lat lon in addition to the geography data type?
It's my first time using Postgis, I have been doing some reading on using it. I have added a geography data type to my table, but the values are stored in a binary format. Is it good practice to also ...
0
votes
0answers
38 views
Query to reduce column length
I'm new to PostgreSQL so sorry if I haven't explained myself clearly!
I've used the below query to find out the max character length of each column in my table. I now want to be able to shorten each ...
0
votes
1answer
40 views
How can I find the longest record (in characters) for each column in a table in PostgreSQL
I couldn't find the answer to this on here so sorry if repeated. I'm looking to query a table to find the longest record in characters for each column in a specific table.
I have a table which is ...
0
votes
1answer
47 views
Indexes for combined time and location data
I have a multi-TB table with geolocation data for a certain item id and timestamp.
It is partitioned by time with a partition for every month (~500GB), and clustered (including the partitions) on the ...
2
votes
1answer
910 views
RDS: permission denied for relation spatial_ref_sys when copying
I am copying tables using ogr2ogr from Oracle to PostGIS. When I run try to load these I get,
ERROR 1: ERROR: permission denied for relation spatial_ref_sys when copying <table>@AWS
0
votes
0answers
35 views
Storing earth observation derived attributes for polygons
I want to generate a number of vegetation related indices (NDVI, NDTI, etc., min/max/avg/std (pixel values)) from earth observation data for a large number of polygons during the entire growing season,...
0
votes
1answer
21 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
1answer
32 views
Three WHERE slow query down
I have a table adjacency, which stores information about connected lines and their angle to each other is build by
CREATE TABLE public.adjacency
(
currentedge integer,
currentgeom geometry(...
1
vote
0answers
113 views
Querying foreign table with SP-GiST index results in “ERROR: cache lookup failed for type 0” [duplicate]
I'm using PostgreSQL 11.3 (compiled by Visual C++ build 1914, 64-bit) and I want to access a table in another Postgres database using postgres_fdw but since the original table has an SP-GiST index on ...
0
votes
2answers
995 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, ...
0
votes
1answer
23 views
How to run multiple ST_DWITHINs/ ST_INTERSECTS within a single WHERE clause
PostgreSQL 11, on Windows 10.
I'm working with a post-gis enabled database.
I want to UPDATE a large polygon table (100,000+ rows) WHERE the geometry of that host table intersects the geometry of ...
0
votes
3answers
134 views
How to SELECT “closest” rows from another table?
I have two tables, map1 and map2, there are multiple possible combinations between the columns map1.id1 and map2.id2.
I have tried the below query:
SELECT map1.id1, map2.id2, MIN(...
0
votes
0answers
61 views
How to optimise PostgreSQL planning time
I have a small query using postgresql 9.6.11 and postgis 2.3.1+dfsg-2
select ST_Transform(ST_SetSRID(ST_Point(671699.9999988,6585300.5001232),32633),3006);
that is slower than I like for where it is ...
0
votes
0answers
60 views
How to properly find duplicate rows with spatial and temporal criteria
I have a table with historical rows indicating the positions in which ocean samples were taken. From this table, I would like to find duplicates based on spatial and temporal criteria. Thus, the ...
0
votes
0answers
349 views
Cache lookup failed for function 1 while activating PostGIS
I'm using PostgreSQL 10.5 on a remote server (Debian). I created a database inside then I attempted to enable PostGIS but it wasn't successful.
I had this error : cache lookup failed for function 1.
...
-3
votes
1answer
122 views
Can PostGIS or Oracle Spatial and Graph scale to hundreds of terabytes of data, or should I use a NoSQL option like DynamoDB? [closed]
I'm going to have a very large data set (a few hundred terabytes big eventually), but it is very well structured, relatively simple data involving lat, long points (which is why I want some GIS ...
1
vote
1answer
134 views
Google Cloud SQL lacks raster2pgsql
Google Cloud SQL supports the PostGIS extension, but I haven't seen any documentation regarding how to work with raster data on it. To load raster data into a table, we have to use raster2pgsql driver....
0
votes
1answer
136 views
Error with pg_upgrade and PostGIS
I have a PostgreSQL 9.2 setup (with Postgis) and am trying to upgrade it to 9.6.
When performing the pg_upgrade, I get the following error:
ERROR: could not find function "geography_analyze" in ...
3
votes
1answer
44 views
How to “group” based on consecutive occurrences of an element in a column in postgres
I have a table that, when ordered, describes a path by point descriptions in each row.
A column tells whether a row is a special (stop) point within that path and is empty otherwise. The path starts ...
2
votes
1answer
848 views
ERROR: invalid GeoJson representation
the GeoJSON Spec show,
{
"type": "Feature",
"geometry": {
"type": "Point",
"coordinates": [125.6, 10.1]
},
"properties": {
"name": "Dinagat Islands"
}
}
But when I wrap that in ...
2
votes
1answer
201 views
How to insert into table multiple geometry rows from function JSON param?
My table:
CREATE TABLE Points
(
id integer NOT NULL DEFAULT nextval('point_id_seq'::regclass),
info character varying(20) NOT NULL,
my_point geometry(Point,4326),
CONSTRAINT ...
