Questions tagged [postgis-2.3]
Version 2.3 of PostGIS, a spatial database extender for PostgreSQL object-relational database, which is targeted for release in Sept 2016.
43
questions
1
vote
0
answers
357
views
PostGIS 2.3 to PostGIS 3 upgrade blocked by changes in ST_Buffer
I'm on a PostgreSQL 10.21 AWS RDS with PostGIS 2.3.7. I need to upgrade PostGIS to PostGIS 3 in order to upgrade PostgreSQL because PostgreSQL 10 is approaching EOL.
When I run ALTER EXTENSION postgis ...
- 3,377
5
votes
1
answer
141
views
PostGIS azimuth of line segments
I initially drafted a workflow that breaks down a road network and categorizes each line segment as by compass direction such as NS (north-south), EW (east-west), NW (north-west), NE(north-east)
The ...
- 379
2
votes
1
answer
194
views
Split polygon with West East direction with equal area in PostGIS
This code splits into NS direction but I need to split with WE direction (horizontally):
WITH foo AS (
SELECT id,name,geom, blade
FROM admin4
CROSS JOIN LATERAL ( SELECT ST_xMin(geom) + (ST_xMax(geom) ...
1
vote
1
answer
407
views
Custom Attributes from GeoJSON files not loading through ogr2ogr to PostGIS database
I am ingesting hundreds of GeoJSON files which I need to load in the PostGIS database. I am using ogr2ogr in a shell script to automate the process on a daily basis using the below command.
ogr2ogr -...
2
votes
2
answers
422
views
How to check two 3D Geometry are equal in PostGIS?
I am using following query for checking the given two 3D geometry are equal. The following two input geometry XY are same and Z is different, but query return true. Is any other method available for ...
- 111
1
vote
0
answers
107
views
How to extract a non contiguous multilinestring portion between 2 points?
I have a non contiguous multilinestring.
I try to cut these multilinestring with points (see schema below)
I tried something like the code below, but the function St_LineSubString and ...
- 143
2
votes
1
answer
260
views
ST_3DIntersection PostGIS incorrect result
I have tried multiple times to obtain the 3D Intersection between a 3D linestring and a solid cube in both PostGIS 2.5.2 and 2.5.3 with the SFCGAL backend.
Below is a query that works as expected and ...
- 41
0
votes
0
answers
247
views
Find a point in a polygon shape
I have a table that contains a column polygon shape. I need a query that I Want to pass a point(coordinates) if point contains in a polygon_shape of an instance the match will be returns .
select * ...
- 101
2
votes
1
answer
752
views
Combining st_snaptogrid with st_centroid and group by using PostGIS?
I have a table of devices, each of them has a field 'location' that is a postGis geometry(Point,4326)
I need to operate a GROUP BY based of the placement on a grid, I'd use ST_SnapToGrid and then I ...
1
vote
1
answer
195
views
ST_ValueCount() returning multiple rows of same VALUE
I want to calculate the proportion of each pixel value in a raster after clipping a much larger raster to my area of interest. However, after clipping and saving the raster from PostgreSQL database/...
- 130
4
votes
1
answer
137
views
Offline editing slides the attribute data over one column and ads an "fid" column [closed]
My QGIS 3.4 Madeira project is attached to a PostGIS database (Azure) and looks good. It contains two layers, one with points and one with polylines. Each item in the Pointz layer has about ten ...
- 885
2
votes
0
answers
367
views
How to tune the performance of Nominatim Reverse Geocoding Application
Running the query directly to the PostgreSQL takes 3-8ms. The PostgreSQL can handle 500 concurrent connections. The Apache server is running on 8 core CPUs. Analyzing the Apache access logs: There are ...
- 121
1
vote
1
answer
123
views
PosGIS KNN returns different results from QGIS hub spoke function
Following example here I have tried to get nearest neighbours where code is;
CREATE TABLE model.hub_spoke_spokes3f AS
SELECT
hub.id AS hub_id,
spoke.id,
spoke.point_geom,
ST_Distance(hub....
- 1,492
2
votes
2
answers
1k
views
Combining two spatial tables in PostGIS
I have two PostGIS tables with geometries. One with number attributes and one with letter attributes. Each table has two features (rows).
I would like to apply a operation on both stables so that I ...
- 3,245
5
votes
1
answer
173
views
Getting the distance traveled across road segments
so I have a query that takes an encoded polyline, segmentizes that polyline into smaller pieces, and matches each point on that segmentized line to a "road segment" from a separate table in my ...
- 115
4
votes
1
answer
723
views
Get closest road-segment to segmentized LineString points
So I have a table that stores a bunch of traffic counts for various road segments across the country. I'm regularly getting real-time location data from vehicles that are moving around a given region ...
- 115
3
votes
1
answer
793
views
ERROR: function st_straightskeleton(geometry) does not exist
I'm trying to calculate center line from polygon, so i'm trying to do with postgis function st_straightskeleton but it is rising the error of function not found.
postgis version: POSTGIS="2.4.3 ...
- 151
0
votes
2
answers
1k
views
ST_Dump and "geometry dump" error
I am trying to dump a set of point/multipoint rows with this query:
SELECT a.gid, a.nid, a.uid, a.cod_reg, postgis.st_dump(geom)
FROM table a
but I get this error
ERROR: relation "...
- 13
0
votes
2
answers
789
views
PostGIS & Python SQL Query formatting: Create Geometry only for entries that are not NULL
I think this is rather a formatting issue. I have a database with a geometry Column that sometimes contains points. Normally, it will be None/Null.
When I format the SQL query in psychopg2 and python ...
- 694
2
votes
1
answer
1k
views
ST_MakeValid() leaving data unaltered?
I recently received a PostGIS instance and am trying to figure out how much of my current processing workflow (in proprietary tools) can be ported over to PostGIS.
One of the most frequent tasks I ...
- 1,455
1
vote
0
answers
357
views
Osmosis Extremely Slow After Postgres 9.6 Upgrade
I am seeing extremely slow processing times for syncing my local osm database with the master repo after a Postgres upgrade to 9.6 from 9.5 (Postgis 2.3 from 2.2). The syncing used to happen every day ...
- 327
1
vote
0
answers
136
views
PostGIS: Query using spatial join using pgadmin3
I have created three tables ch01.restaurants (SRID 2163;columnsfranchise, geom), ch01.lu_franchises (lookup table, no geom field;columnsid, franchise), ch01.highways (SRID 2163;columnsgid, feature, ...
- 631
4
votes
1
answer
5k
views
Could not access file "$libdir/postgis-2.3" on Travis installation
I was searching for a long time to test on Travis my symfony application using postgis extension.
I read the Travis documentation to enabled Postgis and my .travis.yml was this one:
# Project ...
5
votes
1
answer
186
views
Calculating area of intersecting polygons within severals overlapping buffers
I am working with..
an initial point layer (in blue) called : centroid_layer.shp
an initial polygon layer (in green) of woods called : wood_zone.shp
..and I need to get the area of intersected ...
- 169
0
votes
1
answer
366
views
PostgreSQL+PostGIS creating GeoJSON with ST_MakeLine
Trying to get a valid geojson response from my PostgreSQL(9.6.3) + PostGIS (2.3.2) to track routes. I have a table of GPS positions of the form:
CREATE TABLE public.gps
(
id integer NOT NULL ...
- 141
1
vote
0
answers
221
views
Speeding up closest point query?
I'm using PostGIS/Tiger for a mapping solution.
For a given longitude, latitude (derived when someone clicks on a point in my Google Map), I want to retrieve the closest point from my table. My table ...
- 161
3
votes
0
answers
115
views
ERROR pj_get_spheroid_defn EXTENSION POSTGIS2_96 POSTGRES-9.6 [duplicate]
I recently had this error, and surely other people also are going to have when they update the POSTGIS and POSTGRESQL, so I want to share the solution.
OS: CENTOS 6 (x86_64)
Installed: postgresql-9....
- 160
1
vote
0
answers
174
views
PostGIS populating existing table with imported shapefile
I have a PostGIS database with an empty table ch01.highways. With the following code, I want to populate the table:
INSERT INTO ch01.highways (gid, feature, name, state, geom)
SELECT gid, feature, ...
- 631
1
vote
0
answers
202
views
Understanding the performance boost behind type casting in PostGIS
I am running a PostGIS server (version 2.3). I have a table with the following structure:
- index: BIGINT
- geometry: LINESTRING
I was playing with some of my spatial queries (ST_Intersects in this ...
- 185
3
votes
0
answers
99
views
osm2pgsql backwards compatability issue during my biannual elevation contours import process
Every two years when I upgrade to the latest debian stable I start from scratch with my elevation contours pgsql/postgis database. This year I spent an entire day trying to find some new combination ...
- 203
1
vote
0
answers
649
views
Renderer error Could not create datasource for type 'postgis'
I am trying to setup osm on centos 7. I have been following this link http://qiita.com/nkmrtkhd/items/642ae1b8a996cc46d85c. Most of the steps work here but I needed to do some extra googling for ...
- 133
5
votes
1
answer
4k
views
How to use ST_VoronoiPolygons() in PostGIS?
Sorry folks, I know it sounds like a lame question - and, yes I did read the manual... :-)
I'm trying to make Voronoi diagrams around points I have in a PostgreSQL table.
It should be rather easy - ...
- 552
1
vote
0
answers
147
views
Make different buffer around buildings per region - ST_Buffer and ST_Intersection slow query
I am developing Python code to explore suitable sites for wind power development in some countries using spatial data and Postgresql/PostGIS.
For that reason I have tried to process a buffer around ...
- 11
3
votes
1
answer
1k
views
QGIS Seem To Bypass PostgreSQL/PostGIS User Privileges/Permissions
I created a "Read-only" User in PostgreSQL via a Role with "SELECT" ONLY privilege on all tables in a schema as shown below:
GRANT SELECT ON ALL TABLES IN SCHEMA [schema_name] TO [role_name]
GRANT [...
- 61
0
votes
1
answer
82
views
Error using Postgis 2.3.1
I installed PostgreSQL 9.6, PostGIS 2.3.1 and Pgadmin III on my computer, and I was reading the documentation, so I thought that would be a great idea see the function on practice and then modifie ...
1
vote
0
answers
64
views
Bad PostGIS TIGER Geocoding Performance In Specific States But Not Others
Short version: What should I do when I get great geocoding performance for all but one installed state?
Long version: I (not a DB expert) am running the PostGIS tiger geocoder on my machine, not a ...
- 11
1
vote
1
answer
255
views
Strange result with ST_Distance, geography type and equator
I am getting strange results with distance queries in PostGIS using geography types and ST_Distance. According to the docs, ST_Distance should return the distance in metres between two geography ...
- 8,608
1
vote
1
answer
128
views
Distance in polygon from points in postgis?
I've got two GeoJSON files. One is points (gas stations) and the other is the local administrative area. I'm wondering is it possible to calculate the farthest distance in that polygon from the point?
- 683
8
votes
5
answers
1k
views
Finding maximum distance point can be from polygon using PostGIS?
I have a hydrography dataset containing navigable waterways: ponds, lakes, rivers, and streams. I need to find the maximum distance ANY location within the state can be from a navigable waterway. I'm ...
- 3,167
2
votes
0
answers
208
views
Vector layer parameter in QGIS script takes wrong layer
I have 6 postgis layers in my QGIS project and all of them have the same data source but different joined data.
When accessing these layers from a python script with a vector layer parameter (like ...
- 607
2
votes
0
answers
328
views
PostGIS topology: creation of topogeometry polygons for simplification
I am using PostGIS topology to manage a geologic mapping project.
My basic topology is defined by linework. The interstital polygons are meaningful, but can be constructed simply from lines and ...
- 304
6
votes
1
answer
274
views
Duplicate PostGIS view entries in QGIS
I've got a table called "MargamParkMap" - which is made up of polygons. One of the attributes in the table is "authors".
I've created a view with the aim of merging the polygons by author.
What I've ...
- 295
5
votes
1
answer
950
views
Obtaining Each Unique Area of Overlapping Polygons in Postgres 9.6/Postgis 2.3
I have recently finished compiling Postgres 9.6/GDAL 2.2 and Postgis 2.3 and am looking to build a generic, efficient solution for obtaining each unique area of polygon overlap within a given dataset ...
- 1,611