Questions tagged [postgis]

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

62 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
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 ...
3
votes
0answers
36 views

Upgrade PostgreSQL 9.3 without moving the DB and messing PostGIS on OSX OpenGeo Suite original installation

I looked for the answer, but could not find a specifically fitting one, so would appreciate an answer or a pointer to one... Q: How do I upgrade PostgreSQL 9.3 to the current (now 9.6 ?) without ...
3
votes
0answers
117 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 ...
3
votes
0answers
314 views

2 versions of Postgis installed

For some reason I manage to have 2 versions of postgis installed at the same time due to a wrong update. I can see this as by issuing a select postgis_full_version() i get back version 2.1.0 ...
2
votes
0answers
65 views

PostGIS raster2pgsql error

I am trying to import raster data into PostGIS using raster2pgsql and as reference the PostGIS Cookbook. First I created the sql file in the following command raster2pgsql -d -I -C -M -F -t 100x100 -...
2
votes
0answers
38 views

Spatial index on raster tiles not used

I have a table where each row contains a raster covering a different geographic area. I need to query the value of the raster at a given point, but only need to query the raster that covers my lon/lat,...
2
votes
0answers
183 views

Postgresql Filter Slows Query

I've got a query like this: SELECT count(*) FROM acs2014_5yr.geoheader as g JOIN tractcache as c ON (c.tract_id = g.state||g.county||g.tract) JOIN tiger2014.tract AS t ON (t.geoid = c.tract_id) ...
2
votes
0answers
51 views

PostgreSQL: Improve system performance, KNN modification with GiST Index

In my PostgreSQL 9.5beta2, Postgis 2.2.0 compiled by Visual C++ build 1800, 64-bit I am running a Nearest-Neighbor-Query with the additional complication of unique assignment. I am running this on i5-...
2
votes
0answers
344 views

How to optimize inefficient query in PostGIS

I'm doing some queries on a PostGIS, and some of them take a really long time ( > 60 secs). My queries are like this, except querying different tables (like osm_placesbelow). This query takes about ...
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 ...
2
votes
0answers
464 views

PostgreSQL 9.2 (PostGIS) performance problem

A month ago i got acquainted with PostgreSQL and only now i've noticed that some of my queries are pretty slow. Well, for example, i have table: CREATE TABLE sometable ( gid serial NOT NULL, geom ...
1
vote
0answers
62 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
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. ...
1
vote
0answers
27 views

How to make use of indexes with inner join in Postgis?

I got 2 sets of points in 2 separate tables. Table_a got 100k points and table_b got 300k of points. I trying to find nearest points in relation find me any point from table_b that is within 50 meters ...
1
vote
0answers
63 views

Improve the performance of ST_Within boundary lookups in PostGIS

I'm trying to improve the performance of a georeferencing feature in our application. For every address in our system, we need to store which of several boundary types it is located within (local ...
1
vote
2answers
34 views

How to update a point table when a polygon table is modified

I am trying to create a function that update table A (point) when table B (polygon) is modified. The reference to B must be changed in A. I can't get it to work. There are no overlaps in the polygons. ...
1
vote
0answers
261 views

Improve underestimated CTE estimates or replace CTE scan in Postgres 10.1

In Postgresql 10.1 with PostGIS 2.4.1 I select line geometry distributed in space with the help of a 3D bounding box. These edges are then joined against the respective node information table to get ...
1
vote
0answers
143 views

Query on postgres 9.5 several times slower than on postgres 9.1

I am running performance tests on two systems that have the same postgres database, with the same data. One system has postgres 9.1 and the other postgres 9.5. There are only slight differences in the ...
1
vote
0answers
99 views

creating postgresql function with table as input

I have a fairly large spatial query that I wrote using the postgis extension. the query works very well but I would like to make into a function because for easier re-use In concept: this query takes ...
1
vote
1answer
339 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
0answers
88 views

PostgreSQL PostGIS geopoint within angle range

I'm new to PostGIS and I see there are so many options, it's a bit overwhelming. I want to simply find all geo points (stored in ISO 6709 format) within a certain angle of a given geopoint. For ...
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 ...
1
vote
0answers
809 views

CREATE EXTENSION IF NOT EXISTS postgis_topology WITH SCHEMA topology crashes server

I am moving to a new Windows server and upgrading PostgreSQL to 9.6 and PostGIS to 2.3 while I am at it (old versions were 9.2.4 and 2.0). I used pg_dumpall to dump the entire server. Came to 318GB. ...
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 ...
1
vote
0answers
150 views

Add Shapefile loader plugin to PgAdminPortable

I'm using PgAdminPortable and I'd like to enable « PostGIS Shapefile and DBF loader » plugin. The issue is that the files are not structured as in a PostgreSQL complete install (see below). I have ...
1
vote
2answers
262 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....
1
vote
0answers
117 views

How can I connect MapGuide Maestro to PostgreSQL/PostGIS?

I am trying to connect my database PostgreSQL/PostGIS to MapGuide Maestro but it sends me an error message: Do you have some idea about this or can you help me about how I can connect it with my ...
1
vote
0answers
842 views

PostgreSQL suddenly using all my disk space

Today I fired up my linux mint via my virtual computer (host is windows 8), left for 30 minutes, and when I was back, I got an error saying I was out of disk space. Yesterday I had about 20 free GB (...
1
vote
0answers
660 views

Can I export a PostgreSQL database from Heroku and Import as a PostGIS database in Digital Ocean?

Disclaimer: This may be a dumb question. This is honestly way over my head... one of the pitfalls of using Heroku for too long. I have a PostgreSQL database on Heroku. Now I'm migrating to Digital ...
1
vote
0answers
172 views

I am not able to create/reindex a unique index in a table

I have a table that contains user locations (called public.user_location) and is the following: Column | Type | Modifiers id | ...
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
0answers
409 views

PostgreSQL how to create a scalable location-based query

I need suggestions because I am not good enough. I have a DB in PostgreSQL that runs on AWS (Amazon Web Services). I have a table "user_location" where there are stored the locations of each user ...
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
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,...
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 ...
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 ...
0
votes
1answer
101 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
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
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 ...
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
188 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
23 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,...
0
votes
0answers
88 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. ...
0
votes
1answer
26 views

How does PostGIS make its data types compatable with Postgres?

How did Postgres extend to accommodate PostGIS data types? I am trying to create a spatial system. All the code is written in C++. I do not want to create separate data type in Postgres using CREATE ...
0
votes
0answers
25 views

Rename a schema that contains an extension used by another schema

How can you rename a schema containing an extension without breaking tables in other schemas that rely on this extension (PostGIS)? I'm trying to temporarily rename a schema "dest" to "public" in ...
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
3answers
54 views

Help to improve a function

I have this function working fine: CREATE or replace FUNCTION pointscanner( IN source integer, IN target integer, IN k integer, IN directed boolean, IN quant integer ) RETURNS ...