Questions tagged [postgis]
PostGIS is a database extension for PostgreSQL providing additional spatial types, indexes, functions, and aggregates.
214
questions
-1
votes
0answers
17 views
Postgres: handy way/function to compute bearing change from coordinates
I am going to compute point-based acceleration and bearing relative to true/magnetic north from the sequence of GPS points with timestamp and instantaneous speed.
Here's how the dataset looks like:
...
0
votes
1answer
17 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
28 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
16 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
0answers
17 views
PostGIS Issue with not displaying out of DB rasters correctly in QGIS
I am trying to create an out of DB raster from a VRT in postGIS and displaying it in QGIS using DB manager.
I am creating the VRT using absolute paths and have checked using PGAdmin that the DB is ...
0
votes
1answer
28 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
30 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
12 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
32 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
36 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
39 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
312 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
32 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
10 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
16 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(...
0
votes
0answers
33 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
103 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 ...
0
votes
1answer
428 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
21 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
103 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
28 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
58 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
40 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
290 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
29 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
20 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,...
-3
votes
1answer
92 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
81 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
90 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
40 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
447 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
121 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
129 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
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
2answers
83 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
27 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
417 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
901 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
15 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
18 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
206 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
267 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
121 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
3k 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
2answers
71 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
243 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
29 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
81 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 ...
