Stack Exchange Network

Stack Exchange network consists of 175 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
vote
0answers
21 views

Use less temporary space in DWithin join

I only have 12GB of free space. Is there a way to create this table without creating a bunch of temporary files? It runs out of space and never completes. I want to know how many cities are within ...
3
votes
1answer
56 views

QGIS Geometry Generator Line Type

I am trying to create a line style for a multiline geometry. I am hoping to create a linetype which looks like the following The Points are not an issue, its the line/lines entering and exiting at ...
0
votes
0answers
26 views

Reading and processing large shapefile

I have a large shapefile (~10 Gb) which it output of Atoll (RF Coverage) and I need to perform some post-processing on it. I began by importing the file in a file geodatabase, the file was imported ...
1
vote
0answers
37 views

Finding overlapping geometries in the same table using PostGIS [on hold]

I have a table containing about 50,000 polygons and some of them are overlapping. I'm trying to use PostGIS to return a table with all the overlapping parts of the polygons along with some of the ...
1
vote
0answers
24 views

Create continuous line network using PostgreSQL [on hold]

I have shapefile which doesn’t have continuous network. I want to connect it to the nearest node and create continuous line from it. Is there any step by step material which I can followed? or any ...
0
votes
1answer
24 views

PostGIS cannot create extention:ERROR: cache lookup failed for function 1

When trying to create PostGIS extension I am getting this error: ERROR: cache lookup failed for function 1 I am running PostgreSQL 11 on Ubuntu 16.04. I have installed PostGIS and PostGIS scripts. ...
0
votes
1answer
20 views

Deselecting previous selection in OpenLayers?

Previously we did when you click on a geometry on the map to color in another color and zoom on geometry. But now you should deselect the previous selection in the original color in which the map was ...
-1
votes
0answers
8 views

Overlapping points converted into separate distance in ArcMap [on hold]

How can I convert overlapping points data into separate distance in ArcMap or PostGIS? Something like Disperse Markers in ArcMap but this is representation only. I need a shapefile.
1
vote
0answers
31 views

QGIS temporary layer to PostGIS table database

I have an empty polygon table in PostgreSQL/PostGIS database with fields (id,field_1). after some processing in QGIS using pyqgis and processing toolbar I create some temporary layer like this case : ...
0
votes
0answers
25 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
1
vote
2answers
47 views

Load LINESTRING in WKT-format from CSV to pgAdmin?

I've got a CSV file with a column containing LINESTRINGs that looks something like this: 1,"LINESTRING(20.331 54.33, 21.67 44.89, 12.67 13.5679)",2014-03-09 13:30:33,2014-03-09 13:31:33 2,"LINESTRING(...
2
votes
1answer
21 views

Managing custom projections

I am developing a mining sampling database. There will be many project specific, user created projections. Typically they will be Oblique Mercator defining a local coordinate projection system. ...
0
votes
1answer
28 views

Boundary point snapping post process - QGIS/PostGIS [on hold]

I have a set of polygons that have been digitised without snapping I want to achieve this after the event such that shared boundaries share common points. I have QGIS and PostGIS At present the ...
6
votes
4answers
324 views

Identifying “long and narrow” polygons in with PostGIS

I have a set of polygons representing large areas, say city neighborhoods. I want to identify the large overlapping areas between them. But there's a problem: sometimes these polygons will overlap ...
2
votes
1answer
56 views

Using ST_UNION to combine several polygons to one MULTIPOLYGON using PostGIS?

I have a problem with ST_UNION So, I have a several polygons DB looks like this I need UNION all off those polygons to one multipolygon and get result that looks like this I tried use ST_UNION ...
0
votes
1answer
20 views

Text File MultiPolygon/Polygon to PostgreSQL using Python/Psycopg2

I am writing a python script attempting to insert GeoJSON Polygon coordinates into a Postgres table. I am writing a basic insertion code since I am new to GIS domain. The code look like this: ...
1
vote
1answer
40 views

Find all circles that overlap with point

(I'm very new to (Post)GIS in general, and I couldn't find an existing answer to this (or just don't know the right terminology) -- sorry if it's already been asked & answered) I have a bunch of ...
1
vote
2answers
43 views

Smooth Edges of polygon in postGIS

I have a shape that looks like this: It's the end result of a buffer, merge, bugffer and Chaikin Smoothing process in PostGIS. It's almost perfect however I'd like to remove some of the "fissures". ...
0
votes
0answers
32 views

Precision difference MSSQL geometry vs PostGIS geometry [closed]

For a school project I need to sync geometries betwee a MSSQL server and a PostGIS with a Python script (get geometry as WKT, create shapely polygons and compare with .equals). Everything works fine ...
5
votes
1answer
91 views

Create a line string from street geometry

I'm currently facing some hard times trying to create a simplified line from a street geometry. The data that I'm currently using comes from OpenStreetMap, using osm2pgsql.(Which means PostGIS, but I'...
1
vote
0answers
47 views

QGIS and remote PostgreSQL/PostGIS slow performance

I'm using a cloud service for hosting my PostgreSQL/PostGIS database. When connecting with QGIS, its extremely slow. The observed behavior is: Connecting to PostGIS DB: works great. Adding a layer ...
1
vote
1answer
27 views

Problem with checking for Valid Geometries for PostGIS in Azure

I have used this PostGIS in different environments many times without issue: update my_table set geom = ST_MakeValid(geom) where ST_IsValid(geom) = false; However in Azure I keep seeing the ...
0
votes
1answer
25 views

How to read WKB from a PostGIS table with the sf package in R [closed]

I'm working with OpenStreetMap data, for the manipulation of which I mostly use raw SQL queries to a PostgreSQL/PostGIS database. However this is not the most productive workflow, and I would like to ...
2
votes
1answer
72 views

Better performance for geohash aggregation with PostGIS

I'm using the GeoNames (https://www.geonames.org/) dataset and want to aggregate the points in geohash of a specific precision. Beforehand I'm filtering with an bbox. So this is the query I came up ...
1
vote
0answers
27 views

Create a Non-North/Rotated-Vertically User Coordinate System?

I need to plot 3d PostGIS data into vertical planes for symbolization and drawing vertical cross-section interpretations for mineral exploration models. I have written a short plpgsql function that ...
0
votes
1answer
32 views

ST_Within doesn't use BBOX (&&)

I have an PostgreSQL Query to search Points within a polygon: With x as ( SELECT ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[10.2224394042641,53.3854964923405],[9.96469942811056,...
0
votes
1answer
21 views

How to cast Geometry to Geography in PostGIS/Postgres with sqlalchemy + geoalchemy

Some PostGIS functions behave differently based on the arguments types. Example: ST_Distance will calculate projected units distances for geometry arguments and meters distances for geography ...
0
votes
0answers
46 views

Using ST_Intersection with three or more tables

I'm trying to achieve a multiple-table intersect with PostGIS while retaining the geom for each shape. For any given property shape, I want individual cuts of that shape split apart by different ...
1
vote
1answer
66 views

Listing all overlapping polygons using PostGIS?

Assume a table of overlapping polygons where the number of polygons overlapping a single area varies from 1 to n. I am trying to find a PostGIS/SQL query that will return a list of all of the polygons ...
1
vote
3answers
83 views

Is there anything faster than PostGIS ST_Intersects for seeing where features intersect (or something wrong with my approach)?

Basically have 2 tables. 1 is buildings (1809555 rows called oregon). Another is flood zone (164 rows called oregonslr1). Want to see where they intersect to get number of buildings flooded. I am ...
2
votes
1answer
49 views

Identifying Parcels on Street Junction in QGIS

I have the following shapefiles (click here to download them): parcels polygon parcels point streets/row polygon streets line I use QGIS to manually identify polygon parcels with street corners (...
0
votes
1answer
17 views

Django demands to set default. ValueError: String input unrecognized as WKT EWKT, and HEXEWKB

when I try to makemigrate models with Geometry fields to postgres DB I face with next message: You are trying to change the nullable field 'geom' on allotment to non-nullable without a default; we ...
1
vote
1answer
20 views

Using ST_DWithin to see duplicates

I tried it this way but I think using DWithin might be better for this situation. What would be the correct order to find duplicates which exist within 10km from each other? SELECT n, ...
1
vote
2answers
57 views

ST_Union Topology exception despite of valid geometries

I have an isochrones layer and I want to calculate the population lays on the overlapping regions. My first step is to create an intersection between all the isochrones. After that, I try to create a ...
1
vote
1answer
23 views

GeoServer GeoWebCache produces incomplete or partial tile cache

I have looked at previous threads regarding this topic, but did not find the exact problems described - but they might be related - in short: I have the new GeoServer 2.15.0 and configured the ...
3
votes
1answer
94 views

MongoDB - fast & accurate k-NN

We have been testing the effectiveness of the k-NN query in MongoDB and Postgis on New Year City's taxi dataset on two aspects: run-time and accuracy. The staggering outcome is that MongoDB detects ...
1
vote
0answers
21 views

QGIS plugin “Load raster to PostGIS”

I tried to find QGIS plugin "Load raster to PostGIS" but I could not find anything in Plugins section in QGIS. Is there any external resource where is possible download this plugin and after that ...
1
vote
0answers
43 views

Extracting all cities from a state using PostGIS

I have written a PostGIS query on a database with OSM data of the state of Baden-Württemberg. SELECT * FROM public.planet_osm_polygon WHERE boundary = 'administrative' AND admin_level = '8'; So ...
1
vote
0answers
46 views

Enabling editing for QGIS query layer?

I have a large Postgres/PostGIS database table with ~100 million points that I need to have available for editing (mainly, removing points). Currently, I'm trying to use a QueryLayer in QGIS using ...
0
votes
0answers
24 views

pgr_drivingdistance seems to miss a few lines

For multiple stations I am running a pgr_drivingdistance function. Its nearly correct but it has a strange behaviour I can not place. The function seems to leave out some of the road segments around ...
1
vote
0answers
34 views

Shortest path with certain points

I have a warehouse that has many storage yards. I have an order. It has items list and I have to collect them from storage yards. Storage yards are not equal each other. Their length are different ...
2
votes
1answer
46 views

PostGIS - unique constraint for spatial equality

I have a polygon column for which I would like to add a unique constraint that tests for spatial equality akin to ST_Equals so that it ignores vertex order. For instance, I would like these polygons ...
1
vote
1answer
36 views

Improve performance of ST_Extent query

I am trying to make my own vector tiles and I am using Mapbox Studio Classic for that purpose. When I export my dataset, Mapbox Studio will try to figure out the extend of my data and those queries ...
3
votes
1answer
49 views

How to UPDATE a column with cluster IDs in PostGIS?

I have a table with points in it, and I want to cluster them. I had created a separate table to hold the clusters, but I ended up copying all the data from the original table into the new cluster ...
-2
votes
0answers
28 views

Given the OSM data for a state, find its area by writing a PostGIS query

Apologies if this is a silly question, I'm very new to PostgreSQL/PostGIS. I have downloaded an .osm file for the entire state of Baden-Württemberg from here - https://download.geofabrik.de/europe/...
1
vote
1answer
39 views

Size expectations of OSM in PostGIS

I've got a PostGIS database that's been loaded with a couple of the continent downloads from geofabrik. One of these continents is Antartica - the pbf download is 29 MB but the size in the database ...
0
votes
1answer
19 views

Create KML by date

I have the following table that is populated with new data once a day. postgres=# \d modis_c6 Table "public.modis_c6" Column | Type | Collation | Nullable | Default --...
1
vote
1answer
62 views

PostgreSQL PostGIS: append table to another table [closed]

I have a large polygon table (30+million rows) and a smaller polygon table (45,000 rows). Both are PostGIS enabled. The smaller table only contains polygons that do not exist in the large table. Both ...
1
vote
0answers
32 views

ST_AsMVT single layer with multiple Id's

I'm trying to create mvt using postgis by following this tutorial If understand correctly ST_AsMVT is used to create a layer and generated layer will have id. same id can be used to apply styles. ...
0
votes
1answer
33 views

PostGIS create points image

Here is picture form QGIS. There are three layers. Blue dotted lines are warehouse rack layer. Green one is rack sections layer (routable network, created with pgr_createTopology). And third layer is ...