Questions tagged [postgis]

PostGIS is a database extension for PostgreSQL providing additional spatial types, indexes, functions, and aggregates.

Filter by
Sorted by
Tagged with
0
votes
1answer
94 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
20 views

Postgres/PostGIS is returning data from a table that doesn't exist (I'm a newbie — show me why I am wrong)

Ok, so I am following a tutorial (here) for PostGIS. I have uploaded all the example datasets (all shapefiles) into a postgres db, although I have modified the names so they do not all match the names ...
0
votes
0answers
23 views

Matching specific format of string from a column [closed]

I am attempting to select rows in a column which matches a specific string format like so: ["Data Entry"] The portion of my query which I am using to match is like so: Where <first condition> ...
4
votes
1answer
1k views

Does PostgreSQL/PostGIS use R-Tree or R*-Tree?

PostgreSQL and PostGIS offer to create indexes over GiST with pretty much the same code. On 2D items, "This gives R-tree behavior" is what it says in the PostgreSQL source comments. However, it is a ...
0
votes
1answer
20 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
9 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 ...
1
vote
1answer
204 views

How can I create a copy of postgis db from one windows machine to another

I have postgres 9.3 database with postgis extension. I want to create a new database on different computer and import all the tables from the source database. In mssql it would be simple , just ...
1
vote
1answer
87 views

Postgis distance filter does not work as expected

I have a issue with simple PostGIS query. Here is a query: SELECT id, location, ST_Distance_Sphere(location, ST_MakePoint(21.012228699999998, 52.2296756)) AS distance FROM pins WHERE ...
2
votes
1answer
25 views

PostGIS Intersects Query takes lot of time to get the result

I am developing a webmapping application in which I used PosgreSQL with PostGIS plugin as my DB. I have this two tables namely; evidensapp_polystructures and evidensapp_seniangcbr. Both tables ...
0
votes
1answer
32 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
0answers
28 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
112 views

Problem with UNION ALL

I have to process 200 points (lat, long) to calculate the minor distance of same object (street in this case). So, I made a simple query to obtain 1 result, and replicate the same to the other 199. (...
1
vote
1answer
96 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 ...
0
votes
1answer
32 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 ...
0
votes
1answer
102 views

Copy postgis layer from S3 to Heroku

I have a dump of a postgis layer (layer.dump), which I am trying to add to my heroku database (mydatabase). The postgis layer is stored on S3 (https://s3.amazonaws.com/layer.dump). I would like to add ...
15
votes
3answers
8k views

PostgreSQL vs. MySQL: spatial feature comparison

We are the in the process of building out a web application that has a spatial data component. In the beginning our spatial data comparisons will take a given point and return matched overlapping ...
1
vote
1answer
88 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
27 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,...
1
vote
2answers
55 views

Automating Redshift data dump to a PostgreSQL DB every hour

GIS analyst with minimal experience of this back-end stuff but a big task at hand. I have a redshift database that changes multiple times a day. I want to use this "live" data to make web maps and I ...
0
votes
1answer
184 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
0answers
7 views

Migration to PostGIS from Oracle. SDO_JOIN

I have a query in Oracle to select polygons from my_polygon_table that interact or reside inside of a single polygon residing in border_table: Select t1.*, t2.*, sdo_geom.sdo_intersection(t1.geoloc, ...
0
votes
1answer
11 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 ...
2
votes
1answer
649 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 ...
0
votes
1answer
27 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
1answer
2k views

could not load library “/usr/pgsql-10/lib/postgis-2.5.so”: /usr/pgsql-10/lib/postgis-2.5.so: undefined symbol: GEOSFrechetDistanceDensify

I have installed postgresql 10 on centos 7.4 and installed postgis2.5 few months back. The installation was successful and Iam able to use postgis. All of a sudden Iam getting the following error. ...
0
votes
1answer
196 views

PostgreSQL 9.5 and PostGIS on OSX

I have installed PostgreSQL 9.5 on OSX using the EnterpriseDb package. How do I install PostGIS on it? The official PostGIS OSX Page says that: The OSX PostgreSQL/Stackbuilder combination from ...
0
votes
1answer
192 views

Problem migrating PostGIS

I need to migrate Postgres 9.3 + PostGIS 2.1 to Postgres 10. I'm using CentOS 7.X Using pg_upgrade it should be quite straightforward, but the problem is PostGIS. Postgres 9.3 has PostGIS 2.1 ...
0
votes
0answers
22 views

How to create an Index on a raster column?

In a PostGIS enabled DB, I have a 1.7GB table with a raster column rast. I need to create an index on that column in order to speed up my raster queries. I have tried the following: CREATE INDEX ...
1
vote
0answers
60 views

Querying foreign table with SP-GiST index results in “ERROR: cache lookup failed for type 0”

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 ...
1
vote
1answer
335 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 &...
1
vote
1answer
274 views

Howto do a hard upgrade to 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?
1
vote
2answers
259 views

How to dump a postgreSQL/postgis sample database filtered by a spatial extent?

My goal is to extract a subset of my postgres/gis database to create a sample dump which has to have the same data model with relations between tables and views, but which has a smaller spatial extent....
0
votes
1answer
365 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
1answer
18 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
85 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
16 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
57 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 ...
2
votes
2answers
99 views

PostgreSQL query slows due to ORDER BY even with an index

I have the following query to find offices that service a given area. When I run this query without the "ORDER BY" clause, it takes a less than 0.1 seconds. When I add the ORDER BY clause, it takes ...
0
votes
0answers
33 views

cannot have more than 2^32-1 subtransactions in a transaction

I'm trying to run this but after 10 hours I get the error "cannot have more than 2^32-1 subtransactions in a transaction". What is your advice for getting around this issue? I'm hesitant to split up ...
0
votes
0answers
186 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. ...
0
votes
0answers
21 views

Postgis KNN in Postgres 9.5

I'm trying to use the Postgis KNN functionality as described here: https://postgis.net/docs/geometry_distance_knn.html I have one table with all point geometries. The table has many samples, and in ...
0
votes
0answers
19 views

Is the result of ST_Union defined?

With MariaDB 10.1, I get a simplified LINESTRING when I provide two lines can be joined. SELECT ST_AsText(geom) FROM ( SELECT ST_Union( LineString(Point(0,0),Point(1,1)), LineString(Point(1,...
1
vote
1answer
56 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....
-2
votes
1answer
78 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 ...
0
votes
1answer
67 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 ...
2
votes
1answer
35 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 ...
1
vote
1answer
247 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 ...
11
votes
2answers
37k views

PostgreSQL: Cannot change directory to /root

I am trying to copy a table planet_osm_polygon from one database osm to another test. I su postgres and performed the pg_dump. Problem: However I'm getting the error could not change directory to "/...
2
votes
1answer
74 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 ...
0
votes
0answers
86 views

no performance improvement with pgpool query result caching

I'm trying to add query result caching to a PostGIS database that's frequently hit with the same (expensive) spatial queries. Looks like pgpool-II is currently the only well-developed solution. ...