Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [postgis]

PostGIS is an extension for the PostgreSQL object-relational database that adds support for geographic objects.

0
votes
0answers
10 views

How to retrieve GIS data in Multiple Orthophoto at point level

I have been using a python script that can read Data from Orthophoto(*.tiff) of size 300Mb-2Gb. But this is taking 32s per request of lat-long, which is not ok. I have different Orthophotos with ...
2
votes
1answer
18 views

Get exterior ring of a multipolygon using GEOSGeometry or PostGIS

I am trying to get the exterior ring of the multipolygon in the image below using GEOSGeometry (through GeoDjango) or Postgis, but I have not found an easy solution. This is an example of a ...
0
votes
0answers
19 views

How to overlap linestrings with PostGIS or QGIS?

Explanation: I have 2 tables on PostgreSQL, one table named 'city' that have linestrings wich represents streets from a city (using QGIS I downloaded OpenStreetMaps data and imported to PostgreSQL ...
1
vote
0answers
43 views

What is geometry(4326), and can you do Cartesian calculations with unprojected ellipsoidal coordinates?

I know Geography(4326) is an unbounded, and unprojected, and covers the entire world with ellipsoidal coordinates. I get that the math on this system is done on a spheroid. I also get that with a ...
1
vote
0answers
25 views

Performing PostGIS spatial joins with remote host?

I have approximately 600,000 points that must be joined to ~12 million polygons. This process happens weekly, with the potential to happen daily. The polygon data never changes, and is stored in a ...
-1
votes
2answers
39 views

How best to divide line features into equal parts and create continuous buffer zones for them using Postgres/PostGIS and QGIS [on hold]

Having familiarized with an similar question specified on the link Dividing a buffer into segments based on path and distance, the author asked an analogous question whether it is possible to solve ...
0
votes
0answers
22 views

full python 3 script to load shp file inpo Postgis file

I am using Python 3 libraries to programmatically push a shapefile into a remote POSTGIS data base. I retrived the code googling aroung and slighty modifing it. Here the script: import pandas as pd ...
0
votes
0answers
23 views

PostGIS - Nearest Neighbour Join to sum values

I have a table of polygons T1 and a second table of points P1 Each point has a value I want to sum the values of P1 for each T1, with P1 being the closest set of points to T1 At the moment the only ...
-2
votes
1answer
44 views

Can I use PostgreSQL as a database with ArcGIS Pro? [on hold]

I've been with my current job for 2 years now and have been using QGIS with PostgreSQL. We are currently testing ArcGIS Pro noticed that I can connect to my PostgreSQL database that I was using on ...
1
vote
1answer
61 views

Editing PostGIS layer in Lizmap?

I've got a freshly Lizmap 3.2 installation. I can display PG-Layers but I cannot edit them. My setting is: Ubuntu Server 18.04.1 PostgreSQL 10.6 Apache2 Lizmap 3.2 PHP 7.2 I followed this ...
0
votes
0answers
23 views

GeoAlchemy2: Cannot insert row because Function st_geomfromewkt(unknown) “does not exist”

i've been stuck on this problem for quite a while now and I can't figure out what could be the issue here. I have a Postgres DB with postgis extension installed CREATE EXTENSION IF NOT EXISTS ...
1
vote
1answer
40 views

Creating label coordinates in a virtual columns in QGIS 2.18

I'm working from a database (PostgreSQL/ PostGIS). I created a view in QGIS of joined polylines from one table with the ST_union function. I want to label this polylines in QGIS. So, I created 2 ...
2
votes
0answers
25 views

PostGIS Simplification on topogeometry does not respect topology?

I'm testing the code in the book "PostGIS in Action" (2nd ed) on Topology-based Simplification: DROP TABLE IF EXISTS _temp_table; CREATE TABLE _temp_table AS SELECT feat_name, ST_Simplify(topo,150) ...
1
vote
1answer
21 views

How to reset the id number of newly generated PostGIS topology?

I'm trying to create a clean, new topology for testing by discarding the old topology first, i.e.: SELECT DropTopology('ch13_topology'); SELECT CreateTopology('ch13_topology', 32610, 0.05); However,...
0
votes
0answers
8 views

repeated TopoGeo_AddLineString creates multiple entries in PostGIS topology?

I just noticed something strange in PostGIS topology (PostGIS 2.5). When applying TopoGeo_AddLineString() to the same geometry multiple times, it creates more than one entries in the edge_data table. ...
1
vote
0answers
52 views

Recursive query

How can I do a recursive query with these 2 queries: SELECT ptb.* AS pois FROM ways way, pois pta, pois ptb WHERE way.id = 1 AND pta.id = 12 AND ST_DWithin( ST_LineSubstring( way.path, ...
2
votes
1answer
41 views

Expanding Polygon by distance using PostGIS?

I am trying to expand a polygon in a particular manner with PostGIS. I need a new geometry that is a bit like a Buffer, except Buffer produces rounded corners (lots of vertices) when using it on a ...
1
vote
2answers
107 views

Using PostGIS to find which county in California has most settlements in forest?

Using PostGIS how do I find which county in California has the most settlements in the forest? I have point settlements settlements_CA, polygon forests forests_CA and polygon counties counties_CA in ...
0
votes
2answers
59 views

Seeking list of spatial functions available in PostGIS but unavailable in Spatialite?

It seems like most of the classic spatial function available in PostGIS (i.e. ST_Buffer) are usable with Spatialite, but some others don't seem to be supported in Spatialite (i.e.: no such functions: ...
0
votes
1answer
49 views

How to give a table some geometry from a view on QGIS using a join?

So basically I've got this static table that people update information Then I need it to give it some geometry from a view table. This geometry is dynamic, that's why can't just make a new geom ...
0
votes
1answer
19 views

Update geometries with ST_UnaryUnion at TRIGGER

We are currently working on database with multipolygons and we're using QGIS 3. Sometimes it's very useful to work with parts (each page of document is separate part of multipolygon), however at the ...
0
votes
0answers
45 views

PostGIS - How to create an outline of a ship given the geom of the GPS aerial, ship dimensions, true heading

I have a table of AIS positions for a vessel and its true heading. I also know the distance of the GPS aerial to the bow, and the starboard side, and the overall length and width of the ship. I want ...
1
vote
1answer
57 views

Very slow loading of PostGIS raster layer in qGIS

I'm trying to use big raster dataset with PostGIS 2.5 on PostgreSQL 11 on Linux for the purpose of serving a base map organization-wide. My dataset is 2TB of TIFFs. I created 8GB test table (including ...
0
votes
1answer
32 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....
0
votes
1answer
21 views

Individual point attributes PostGIS Multipoint

I'm coming from a heavy Esri background and I'm trying to figure out how to create a multipoint feature in a PostGIS database that allows me to set a unique attribute for individual points in a ...
2
votes
1answer
41 views

PostGIS geometry to GEOS geometry without lwgeom dependency?

I've been looking for ways to convert PostGIS to GEOS geometries without linking to or dependency on lwgeom/liblwgeom. The closest thing I can find is a mention of the pointcloud approach here: ...
1
vote
1answer
47 views

Efficient way to find polygons with no neighbours, eg, detached houses

I have a map of buildings, I'm looking to identify only those buildings which are detached (no neighbours). I can run select by location touches in QGIS and invert this but it does not seem the most ...
0
votes
1answer
19 views

ST_contains between two tables returns all results of one table multiplied by the number of results from other table

I have one PostGIS table of polygons like this: polygon_id | polygon_geom And another of points like this: point_id | polygon_id | point_geom I want to find all the points from the points table ...
1
vote
2answers
66 views

PostGIS intersects and case function

Hi i've been setting up a script to assign values depending on one type of polygon intersecting with another polygon. I have two feature tables - one with building footprints and one with polygons ...
0
votes
0answers
25 views

How to set the extent of a PostGIS geography table?

I have multiple geography tables in PostGIS database. All of them have extents of (-180,-90;180,90), probably in their metadata, which makes editing difficult. I'd like to set the extent to exactly ...
0
votes
0answers
14 views

Stop detection on a dataset of (id,geometry points,timestamp) to be transformed into meaningful trajectories

My pretty large dataset consists of AIS transmitted messages. I load the dataset to a table in my db. Among other columns in this table, there are a longitude and a latitude column. I already ...
0
votes
0answers
42 views

GeoServer error - “Failed to upgrade lock from read to write state”

I have migrated my GeoServer + PostGIS to another machine. In terms of geoserver I start new instance of GeoServer and copied all content to data_dir. After this operation I have observed that some ...
0
votes
0answers
36 views

PostGIS install Ubuntu: GEOS problems

Using apt-get install, I've added the following packages: postgis/bionic-pgdg,now 2.5.1+dfsg-1.pgdg18.04+1 amd64 [installed] postgis-doc/bionic-pgdg,now 2.5.1+dfsg-1.pgdg18.04+1 all [installed,...
0
votes
1answer
35 views

Converting geography types in PostGIS? [duplicate]

I have multiple tables in different geography types. For example, some are Point, some are PointZM, some are MultiPoint, etc. I need to convert all of them to PointZ. I have searched the documentation ...
1
vote
2answers
37 views

How to convert MultiLineStringZ object to MultilineString object?

I have CAD-data which I transformed to shapefiles. Currently after the transformation the objects still have a Z value which is useless to me. My question is: How do I convert a MultiLineStringZ-...
0
votes
0answers
20 views

Multi-polygon shapefile geometry incorrect when imported as FC in gdb

I am attempting to import a multi-polygon shapefile (the shapefile was generated from a postgis table, I did not generate this file) into a file geodatabase manually (right click, select import, run ...
0
votes
1answer
31 views

Reverse driving distance

I am used to using pgr-route driving distance function to calculate how far I can go from a given point to a surrounding area Assuming that I have a transport graph that includes costs and reverse ...
0
votes
0answers
23 views

What is this PostGIS query doing to measure the distance? How distance value is differed by the_geom and the_geom_webmercator

I dont know how the distance is differed based on WGS84 and Web Mercator. Sample: SELECT *, ST_Distance( the_geom::geography, CDB_LatLng(35.68879039, 139.74517892)::geography ) /...
0
votes
1answer
27 views

PostGIS Connection In QGIS. Vector Query Results of GeometryCollection Load Layer as Raster

https://postgis.net/docs/ST_VoronoiPolygons.html I am trying to load this query result using PostGIS 2.5 connection in QGIS 3.4, QGIS 2.18. SELECT ST_VoronoiPolygons(geom) As geom FROM (...
1
vote
0answers
67 views

PostGIS - Dynamic Server-Side Clustering based on Zoom level

I'm working on a Ruby on Rails application that is showing road signs on the map(leaflet). So, user can browse map and road signs, great. But, if there are a lot of signs and user wants to zoom out, ...
1
vote
1answer
24 views

Missing attributes while copying features from a shapefile to PostGIS layer QGIS

I'm trying to copy some features from a shapefile into a PostGIS layer loaded in QGIS. I followed the steps bellow. Select and copy features from SHP. activate editing of destination layer and ...
0
votes
0answers
37 views

GeoJSON: Rendering 3D building in Cesium via GeoServer

I am trying to make a 3D web application which should be able to render 3D building using Cesiumjs and loading data from PostgreSQL/PostGIS via GeoServer. I have MULTIPOLYGON Z and MULTIPOINTS Z for ...
1
vote
0answers
35 views

PostGIS geocoding with local street centerline data

I have the PostGIS TIGER geocoder setup and working to geocode addresses in a single city. I am trying to use local street centerline data for geocoding instead of the TIGER data because it is more ...
3
votes
2answers
60 views

Distinguish features by distance

----------x-----------------x----------------x--------------- --------------x-------------------x-------------------------- This is a Two-way road, each line stands for one way, and the x means a ...
2
votes
1answer
40 views

PostGIS GeoJson Query Not Validating

I am using the following query to create a GeoJson response: SELECT json_build_object( 'type', 'FeatureCollection', 'features', jsonb_agg(feature) ) FROM ( SELECT ...
0
votes
1answer
28 views

Creating osm2pgsql hstore tags using PostGIS column

This is the opposite question to this one: Creating columns in PostGIS using osm2pgsql hstore tags First, I changed default.style to make separate 'lanes' column. Then I extracted osm database ...
1
vote
0answers
61 views

Cluster points into evenly numbered groups

I need to take x number of points and equally/spatially divide them into groups. Meaning that if I had 40 points like in the image below and wanted to have 4 groups of points. Each group should have ...
2
votes
1answer
21 views

How to generate a correct HTTP response for a pbf within AWS API Gateway?

I am attempting to implement this approach to serving vector tiles from PostGIS using a lambda function and an AWS API Gateway Method: https://www.sparkgeo.com/blog/vector-tile-server-using-postgis. ...
3
votes
1answer
51 views

How should arrays be stored in PostgreSQL when used as a datastore with GeoServer 2.14+?

I am storing a string array in PostgreSQL to serve through WFS in GeoServer 2.14.1. As a test, I have two columns in my table; one column is a character varying array and this other is a text array ...
0
votes
1answer
27 views

shp2pgsql creates one dimensional shapes

I use shp2pgsql to import an .shp ESRI shapefile into PostgreSQL database. I use brew installed shp2pgsql. I used this command first: shp2pgsql -I -s 4326 /path/to/shape_file.shp public.os_gb | psql -...