Questions tagged [postgis]
PostGIS is a database extension for PostgreSQL providing additional spatial types, indexes, functions, and aggregates.
2
votes
1answer
21 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 ...
0
votes
1answer
15 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
32 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
21 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.
...
1
vote
1answer
190 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
2answers
67 views
Joining relations based on distance between geometry columns?
I have two tables:
User(id)
Region(id, userID, origin Geometry('point'), radius))
User has a one-to-one relationship with Region.
I want to query all users that have a region that contains a point (...
0
votes
1answer
21 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
1answer
400 views
Improve performance of this query finding nearby locations
I am trying to execute this SQL, but the query is really slow (almost 2 hours), It's actually querying 1 million records (property_ids), that's why it's slow. But can someone suggest me a strategy to ...
1
vote
2answers
302 views
Undefined symbol when migrating to Postgres 11 [closed]
After upgrading Postgres 10 to version 11, by using pg_upgrade, I get this error:
could not load library "$libdir/postgis-2.4": ERROR: could not load library "/usr/pgsql-11/lib/postgis-2.4.so": /...
0
votes
1answer
14 views
Finding road segements which are not split at intersection.(Query Optimization )
I have a query in which I am trying to find the road segments which intersect but are not splitted at intersection,I have also created gist index on geom column. Here is postgerSQL srcipt
SELECT a.id ...
1
vote
1answer
13 views
Import Postgres 8.3 database in Postgres 10.5 [closed]
I have a database in Postgres 8.3 (and PostGIS 4.2), which I need to import in Postgres 10.5.
I am a DB newbie and would appreciate any pointers to get started. Would you recommend a particular book ...
0
votes
1answer
14 views
select query on postgis not working for specific fields [closed]
So I have what seems to be a really weird problem.
I have imported a table from QGIS into a postgresql + postgis database.
I simply want to select a few columns from that, but I can't.
whats funny ...
0
votes
1answer
59 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 ...
1
vote
1answer
49 views
Postgresql bad planning with multicolumn gist and search with empty results
I'm having an issue with a multicolumn index (integer, geom), where geom are PostGIS points, and the integer is a point aggregator with business meaning. I do searches by boxes in the geom column, ...
2
votes
1answer
190 views
Dynamically define a RETURN table (column type, name) for subsequent loop
I would like to execute ST_Intersection(clipper_geom, clipped_geom) on number of rows coming from a table.
https://postgis.net/docs/ST_Intersection.html
https://postgis.net/docs/ST_Intersects.html
...
1
vote
1answer
37 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 ...
2
votes
1answer
80 views
PostgreSQL Geo Spatial Query is slow
After given up with MySQL i have tried Elasticsearch and now wan't to see if I could go with PostgreSQL/PostGIS that could allow me to go with PostgreSQL only.
I need to fetch records from a Table ...
1
vote
0answers
25 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 ...
2
votes
2answers
45 views
How to verify that at least one point fall into a polygon with Postgis?
I have two tables where table A has a geometry (4326) column that contains surfaces (~20M). A second table B has ~ 500K points (4326). What I want to do is "flagging" (adding a boolean column) every ...
3
votes
2answers
78 views
Joining tables based on time and distance: PostGIS with PostgreSQL?
I have a table with several thousand rows that contains time and position data. Rows with the same group_id have the same timestamp. Lets call this table "Small":
Small Table:
id event_time ...
1
vote
1answer
30 views
In PostGIS, how do I swap the lat/log of invalid points, like MySQL ST_SwapXY?
MySQL has a function ST_SwapXY which swaps the x and y values (long, and lat). A function, like ST_SetSRID, that's useful if you import data incorrectly.
Accepts an argument in internal geometry ...
1
vote
0answers
21 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
79 views
Slony Replication in PostgreSQL
Objective : Set up two DB server if first server down then second can be up and running provided condition is that at evening time both server need to sync. with each other
I am new in DBA and we are ...
0
votes
0answers
20 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 ...
1
vote
2answers
69 views
error query did explode the postgres database
I had some problems last month. I had a bad query in Postgres (PostGIS) of a user launched during the night. This query took all the memory and I had 0% of free disk.
Fortunately, I restored the ...
0
votes
1answer
64 views
Nearest neighbours (KNN) from a selected point
I have a small point dataset, I perform the following I get the 10 KNN from the coordinate. How do I first select a point from the dataset and with that select the 10 nearest neighbours (not including ...
0
votes
0answers
54 views
what version of postgresql 9.6.8 with what version of postgis
I am a bit confused with the upgrade of a dataserver postgres from 9.3 to 9.6.8. Despite the readings of http://postgis.net/windows_downloads/ and the board (not up to date) https://trac.osgeo.org/...
0
votes
2answers
109 views
How can I manage an exception generated by Postgis?
I am running the following query on Postgres 10.3/Postgis 2.4:
create table table_c as (
select A.*, B.area_name, B.id
from table_a A
left join table_b B
on ST_Contains(A.geom, B.geom)...
0
votes
0answers
33 views
How can I optimize a query on two very large tables with array joins?
I have four tables: blocks, boundary, net_a, and net_b.
The blocks table has three columns of interest:
blockid varchar(15)
net_ids int[]
geom geometry(polygon)
~ 6,000 rows
index: blockid
index: ...
2
votes
2answers
224 views
Support for libprotobuf-c on Amazon RDS
I created a PostgreSQL 10 instance on Amazon RDS with the PostGIS 2.4 extension in my database enabled. I want to test the performance of Amazon RDS as a dynamic vector-tile server.
This hinges on ...
2
votes
1answer
72 views
For Postgres, efficiently get points within X distance of point Y?
I have this table as below in Postgres:
create table locations
(
id serial not null
constraint games_pkey
primary key,
name varchar,
location point,
)
;
I want to ...
0
votes
1answer
40 views
How to delete constraint inside a trigger function
I have read a lot about it, but being new to Postgres, I am at a loss.
The trigger is :
CREATE TRIGGER foopolygon
BEFORE INSERT OR UPDATE OR DELETE ON polygon
FOR EACH ROW EXECUTE PROCEDURE maj_foo(...
0
votes
1answer
26 views
Create a check constraint across multiple schemas in PostGIS [closed]
I'm a little new to this so apologies up front if my vocabulary is incorrect.
I'm trying to create a check constraint that determines whether a new point is located within an existing polygon. The ...
0
votes
0answers
18 views
Deleting a polygon and the references to it in an underlying point table
I am trying to create a trigger to erase a polygon reference inside a point feature when you erase the overlapping polygon. The rest of the function handles the insert and update operations. I can't ...
0
votes
0answers
19 views
Tilemill (tileoven) Postgis plugin: Null Connection and subsequent other errors
I am trying to build an entirely Postgis tilemill project aimed at generating state-specific (i.e. Pennsylvania) .mbtile layers. Many of these layers are quite large national datasets that are ...
1
vote
2answers
27 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
1answer
34 views
Joining temporal and spatial data
I have two tables in psql that I am trying to join based on certain attributes - they are very large - 17 M rows and 2.7 M rows respectively
mydb=> SELECT reltuples::bigint AS estimate FROM ...
2
votes
1answer
555 views
Installing PostGIS in a different schema causes topology extension to not find geometry type
I have a sql dump of the schema of an existing database that is working correctly. When I try to load this into a new, empty database psql new_db < dump.sql, I get the following error:
ERROR: ...
1
vote
2answers
71 views
Can't get this PostgreSQL query to run faster
I'm running PostgreSQL 9.6 with PostGIS 2.3.3
I'm trying to make this rather critical query go faster (looking for users within 1000 meters of a location), but I'm having troubles setting up the ...
1
vote
1answer
32 views
PostgreSQL/PostGIS ERROR: Third argument must be a POINT
I have a table mytable structured as follows:
id nv_bez parent_rv lfd_nr hierarch_bez snr geom
1 NV064764 None 1 NV064764_1 1 ...
0
votes
0answers
570 views
postgresql increase work_mem for query
I have a geometrically intensive query that takes about 7+ to run. My database is only used by me and is not shared with anybody else. I have been reading up about the idea to increase the work_mem to ...
0
votes
1answer
86 views
Why PostGIS GIST index is not used? [closed]
I am using PostGIS 2.2.1 with PostgreSQL 9.5.10. I have a table with a geography field for coordinates and I am making a simple bounding box query. The table has 1029 rows. EXPLAIN says it does a ...
1
vote
0answers
180 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 ...
0
votes
2answers
89 views
Is it possible to schema-qualify an operator?
Let's say I create an extension, like PostGIS, in the schema postgis,
CREATE EXTENSION postgis WITH SCHEMA postgis;
Is it possible to fully qualify an operator like && in the below example,
...
3
votes
1answer
496 views
&& vs ST_Intersects performance
I am trying to figure out points reside inside an rectangular area (envelope). I am having a little bit difficulty understanding performance implications of using && operator compared to ...
2
votes
1answer
322 views
Faster spatial queries for 100M records with Postgres
I am currenty using Postgres/PostGIS (one single instance) to run some geo queries to catch points that fall within specific shapes along with some extra data from the relative columns.
However even ...
0
votes
1answer
359 views
Errors encountered while Dump/Restoring PostGIS DB from Postgres on EC2 into RDS
I am migrating from a manually managed database on Postgres 9.4.10 and PostGIS 2.1 to an RDS instance on Postgres 9.4.9 and PostGIS 2.1.
The restore seems successful, but the output gave me 27 ...
2
votes
3answers
441 views
GIS Point or discrete longitude and latitude columns?
I see that the Point type is made to store geometric coordinates, however I don't see what problem it is trying to solve, or what advantages it offers over simply using two numerical columns. It's 16 ...
1
vote
1answer
371 views
What's stored in spatial_ref_sys?
Should I copy data in this table when migrating to the other server?
3
votes
1answer
20 views
How can I modify an existing column into a foregin key in postgres?
Given for example the following:
CREATE TABLE agency(agency_id, agency_name)
AS VALUES (1,'Test Agency');
CREATE TABLE employee(employee_id,agency,first_name)
AS VALUES (16,'Test Agency','Paul');...
