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.

-1
votes
1answer
33 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 ...
0
votes
1answer
22 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
35 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
20 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
50 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
105 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
18 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
38 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
56 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
29 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
40 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
46 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
65 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
24 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
38 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
34 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
19 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
64 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
23 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
34 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
34 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
59 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
27 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
60 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 -...
2
votes
1answer
72 views

Not able to install postgresql-10-postgis-2.4 in Ubuntu 18.04

I am not able to install postgis. $ sudo apt-get install postgresql-10-postgis-2.4 libgdal20 libhdf5-100 libnetcdf13 libgfortran4 Reading package lists... Done Building dependency tree ...
2
votes
1answer
36 views

LineLocate a sequence of points, preserving order

I have a linestring, which represents a bus route, and a sequence of points in order, representing the coordinates of each stop of that route. It kinda looks like this (pardon my poor drawing skills): ...
1
vote
1answer
28 views

ST_Intersects one polygon with layer - always returns 0 results

I'm trying to write a query to find if any features in a layer intersect with a certain polygon, but it is always returning 0 rows. I've tried messing with SRIDs, different geom types, sizes, shapes, ...
1
vote
0answers
17 views

parse_address Error when using Street2 splitter '@'

I'm trying to use parse_address to parse client data and am finding that the split code "@" to tell it where to split address 1 and 2 gives erroneous results when used. If used, the street number is ...
1
vote
0answers
39 views

PostGIS and connection to QGIS issue?

I have a problem with PostGIS and a connection to QGIS. I successfully connected to a database on a server but when I try to add a PostGIS Table, I am getting warning symbols next to all my tables. I ...
2
votes
0answers
38 views

How can I use the SFCGAL version of ST_Union?

I'm trying to combine a bunch of geometry boundaries to get the linework of their borders, but I'm getting an error with the default, GEOS-based ST_Union: SELECT ST_Union(ST_Boundary(geom)) FROM ...
8
votes
1answer
344 views

How can I optimize a Point in Polygon query for millions of points when most of the points lie within the polygon?

I have 150 million points in a point table and would like to find the few points lying outside a given polygon geometry. I know that 99.9% of the points are within the polygon geometry. I am ...
0
votes
1answer
23 views

Intersect lines and polygons [closed]

I am tring to intersect lines with a polygon grid, but i got an error when i run this: create table "st11_01_vd" as SELECT ( "grid_1km.gid", "st11_012017_ln.linetime", ST_INTERSECTION("...
0
votes
0answers
34 views

GeoPandas ParseException: Invalid HEX char error

I am trying to read a geospatial table from a PostGIS database on PostgreSQL. Using the funcion sql = '''SELECT ST_AsText(geom) AS geom FROM my_table;''' df = gpd.GeoDataFrame.from_postgis(sql, ...