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.

3
votes
1answer
32 views

Making UPDATE for coordinate does not return the same value

Being totally new with PostGIS, I am trying to UPDATE one of my column on my table. The column itself is called 'center_point' with type of GEOGRAPHY(Point,4326) UPDATE cities SET center_point = ...
0
votes
1answer
40 views

Lines overlaping in same layer - PostGIS

I have lines overlapping in one layer as in image below. How do I delete one of these lines and leave the other one in the dataset?
4
votes
2answers
85 views

PostGIS difference and union

I have two tables, A and B. I want to Union(/Dissolve) B and then difference A,B. To get whatever is left of A when overlapping B is erased: select a.someid, st_difference(a.wkb_geometry,st_union(b....
1
vote
1answer
57 views

PostGIS -> LineString + PointZ = LineStringZ

DATA I have 2 tables in PostGIS: linestable with MultiLineString geom surveypointtable with Point + elevation attribute GOAL The goal is to rebuild the lines as 3D replacing line's vertices with ...
0
votes
1answer
25 views

Postgis SQL schema for features expected by GeoTools

I am building a small application which uses postgres and has a couple of tables which have a postgis polygon as one of their columns. I was thinking of using a Geotools DataStore rather than querying ...
3
votes
1answer
98 views

Select only lines that touch both sides of polygon - PostGIS

I want to select lines that only touch both sides of a polygon. Is there a function in postgis that can do this? See image below. I only want to select lines from the circle on the left where the line ...
0
votes
0answers
91 views

Extend line on both sides until it reaches polygon boundary - PostGIS

Is there a function in PostGIS where I can extend a line on both sides until it reaches the edge of a polygon boundary?
4
votes
1answer
59 views

PostGIS St_AsMVT latitude incorrect at low zoom levels

I'm using the St_AsMVT feature in Post GIS to create mapbox tiles. This works ok at high zoom levels (>9) but at lower levels the latitude appears to be significantly off...which gets worse as the ...
2
votes
0answers
46 views

Upgrade PostGIS from 2.3.3 to latest version

Current I have the following Version of PostGIS POSTGIS="2.3.3 r15473" GEOS="3.5.1-CAPI-1.9.1 r4246" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.1.2, released 2016/10/24" LIBXML="2.9.4" ...
0
votes
0answers
32 views

Can any suggest an open source workflow to convert an Esri LiDAR terrain dataset to a LAS file

I’ve got a very large file geodatabase (1TB) that contains LiDAR data stored as a multiple terrain datasets. I’d like to spatially filter the data and export the result as a LAS file without using ...
5
votes
2answers
1k views

How to resolve the “ERROR: could not open extension control file” when "create extension postgis;' for PostgreSQL 10?

I am receiving this error when I create extension postgis;: SQL Error [58P01]: ERROR: could not open extension control file "C:/POSTGR~1/10.5/pg10/../pg10/share/postgresql/extension/postgis....
1
vote
0answers
49 views

Query PostGIS raster table in QGIS

I would like to know if it is possible to query a PostGIS raster directly in QGIS in the same way one can use the query builder to filter a vector layer? I have successfully built database views of ...
1
vote
1answer
36 views

ST_SymDifference with dblink

I want to create a table (table_diff) that is a result of symmetrical difference (ST_SymDifference) between geometries (linestrings) from 2 tables (table1, table2) stored in 2 databases (db1, db2). ...
1
vote
0answers
31 views

Problem converting Alaska to svg because of longitude wraparound

I'm trying to render an svg of Alaska, but because the left side of Alaska's longitude is 179 and the right side of Alaska is -152, the left of side Alaska is separated and shows up in the very far ...
3
votes
1answer
107 views

Python/PostGIS - Type “Geometry” Does Not Exist

I am a total novice with both PostGIS and Python - I am just trying to get this game running. I have followed the instructions here and here and am now at a dead end. I have pgAdmin 4 downloaded and ...
2
votes
0answers
36 views

ST_ConcaveHull recomposes polygon without much regard to concavity?

What am I missing in understanding this behavior of concave hull? My primitive understanding is that it applies a "shrink wrap" algorithm. But consider this example where I decompose a polygon into ...
0
votes
0answers
18 views

raster2pgsql as stand-alone application under Linux

I'd like to be able to install raster2pgsql detached from a full PostGIS installation under Ubuntu 18.04. If necessary from source. I couldn't find any guide on that topic. Background: I'm in the ...
1
vote
1answer
42 views

PostGIS Centroid coordinates (E, N)

I would like to add two new columns in the table (E, N), fill in the coordinates of a polygon centroid using PostGIS SQL code and have the column upadated as soon as I save the edit of a drawing in ...
1
vote
0answers
51 views

Display and aggregate time series based on map selection

My data is geospatial and consists in records of concentrations that are measured every day on fixed locations. I would like to display the time series of these records when I select one point of the ...
0
votes
2answers
36 views

What is the simplest approach to symbolizing points from a PostGIS-sourced layer by age?

I have a view in PostGIS that has a bunch of points and I want to symbolize in QGIS (3.2.2) by the age of the points. I am trying to use this expression (or something like it): day(age(ts, now())) ...
1
vote
2answers
52 views

Refresh ST_Area in QGIS

I added a new column in my PostgreSQL/PostGIS database for getting surface area. I can open as a layer in QGIS, but when I draw a new polygon the area column is not updated, is there a way to have it ...
3
votes
2answers
92 views

Is there a Java API for in-memory geo indexes?

My application is backed by PostGIS. There is a particular set of tables which have one of their columns being a geographic polygon. They are relatively small in size (few thousand rows) and can ...
4
votes
1answer
134 views

Returning US Census geographies within range of coordinate using PostGIS and Tiger Decoder?

TL;DR: How do I write a query in SQL (postgres, postGIS, tiger extension) that will return census geographies (blocks, tracts, zcta) within an X mile radius of a lat/lng coordinate? Background: I ...
0
votes
1answer
53 views

ST_DISTANCE isn't giving the expected result

I don't get why ST_DISTANCE is giving me the same closest node when clearly it is not the one nearest to the starting point. Also, using 2 different starting points, I still have the same result. My ...
1
vote
1answer
28 views

Update Set_Area from geometry

I am very new at PostgreSQL / PostGIS. I have got a table with geometry column set in (Polygon,32733), so I would like to update the Area column so as to see the area of each polygon. I tried this: ...
0
votes
0answers
48 views

Problem with connecting to another computer in the local network PostGIS

I've a problem with connecting PostGIS database to another computer in LAN. All the computers involved are running Windows 10. I changed the pg_hba.conf file: IPv4 local connections: host all ...
1
vote
0answers
36 views

rt_raster_from_two_rasters: The two rasters provided do not have the same alignment

I have a set of geometries in my database which I used to create a set of rasters (each raster has a unique color). Now I would like to merge all of these rasters into one but get an error such as : ...
2
votes
1answer
64 views

Dissolving polygons on distance condition using PostGIS?

I'm tying to group some polygons in same layer, on geometry & attribute conditions: within 0.8m from each other and with same value in a column. With answers I've found here I've got something ...
0
votes
1answer
12 views

st_ delaunay returning empty geometry from 3D points

The Data I have a 3D point dataset, one row looks like this SELECT ST_AsEWKT (geom) FROM pc_processing.top_dist SRID=32635;POINT(580992.318 4275265.0982 191.284) ... +1000 more The problem I want ...
0
votes
1answer
57 views

st_union for joining overlapped areas

I have two land use maps for London which have an overlapped area. Can I use the st_union function in postgis to join them together? The attribute table contains information includes legends and ...
1
vote
1answer
31 views

pgr_alphashape returns a self intersecting polygon

After using the function pgr_drivingdistance() I am attempting to make a polygon with pgr_alphashape. Following the example found in the documentation I should do the following: https://docs.pgrouting....
0
votes
1answer
37 views

Why does converting the_geom into lat/long gives me more than 1 coordinate?

I'm converting my the_geom into lat/long coordinates but I only want a single coordinate since I'm gonna use the coordinates to draw a polyline in google maps. Attached is the picture of the result of ...
0
votes
1answer
122 views

Import PostGis tables into a GeoDataframe unexpected : 'AttributeError: 'NoneType' object has no attribute 'encode''

When I try to import two of my tables from postgis to geopandas I have an error. I guess the problem is due to my geometry columun, but I don't know why. engine = create_engine('postgresql+psycopg2://...
0
votes
1answer
26 views

PostGIS ST_RECLASS using Lookup Table not working properly

Link to question/answer similar to what I am trying to do I am trying to run ST_RECLASS on a raster to retain certain pixel values while setting others to NoData(0). I have a function that creates a ...
0
votes
2answers
55 views

How to optimize this query for (OpenStreetMap) map tiles rendering?

I'm trying to optimize my OpenStreetMap map tiles rendering. This is one of the queries renderd makes to PostgreSQL: SELECT ST_AsBinary("way") AS geom, "type" FROM (SELECT way, ...
0
votes
0answers
58 views

Qfield crashes when adding a point for PostGIS layer

I'm working with Qfield and PostGIS layers. Before, we've had ready made PostGIS layers and we've just modified their attributes. Works just fine, despite occasional random crashes. But now that I ...
1
vote
0answers
90 views

What's the most size-efficient spatial database [closed]

I'm configuring a tile server on Apache Mesos. From all the tutorials I've read, I gathered that PostgreSQL and PostGIS is widely used for this purpose. I learned that MongoDb and MySQL Server also ...
0
votes
0answers
15 views

Adding a band in tiff file using PostGIS

The code below exports a tiff with a single band. I would like to put the data for each year in a separate band. How can a band be added in a tiff file using PostGIS? SELECT oid, Lowrite(...
1
vote
0answers
55 views

Create raster layer from points with PostGIS

I'm pretty new to this part of GIS; I have a layer of points that represent cell phone towers; with a given distance to simulate coverage, how can I transform that represented coverage into a raster? ...
1
vote
0answers
37 views

Moving points inside to polygon using PostGIS? [closed]

I have an address and building table in my PostgreSQL. There are some address points outside building polygons, the constrain is that all address must be inside of nearest polygon. Is there a ...
1
vote
0answers
23 views

Select nearest 3D point with sfcgal

The Data Two sets of 1000 points A.geom and B.geom in PostGIS The Problem From Set A take the first point and find its nearest neighbour in Set B Calculate the 3D distance between these two points ...
0
votes
1answer
69 views

PostgreSQL Query which returns geom column as WKT set in function

Given: We are accessing a PostgreSQL DB from a VB NET application. When the query completes the DB rows are delivered in a VB NET DataTable. The Postgre DB geom column is in the format of System.Bytes[...
0
votes
1answer
120 views

Performing kNN between two 3D sets of points (pointclouds)

I have two overlapping pointclouds, for this example I take the first 10, A[0:10] and B[0:10]. They are stored as tuples, [(x, y, z),...]. I want to compare the two based on their nearest x,y,z ...
0
votes
1answer
33 views

Create a materialized view with buffer zone from point layer [closed]

I would like to create a process on PostGIS where users can create a point on a working layer ("working_point_layer") close to a reference point layer ("ref_point_layer"). As I can't set the ...
0
votes
1answer
84 views

PostGIS metric buffer [duplicate]

I'd like to form a 500 meter buffer around a coordinate. That coordinate is in WGS 84. I've been playing with this for a little and seem to have a solution, but is seems way too complicated: SELECT ...
2
votes
2answers
50 views

Performing multiple intersections between two polygon tables and one raster table in PostGIS?

Trying to perform an intersection of an intersection in PostGIS. Its an intersection between a point table, polygon table and a raster table with different number of rows. The polygon table is the ...
0
votes
1answer
64 views

ST_Intersects returns true while ST_Contains returns false for a point located outside of polygon

I am seeing some inconsistent results with the below query. We are testing if a point intersects a multipolygon. ST_Contains returns false as expected but ST_Intersects returns true incorrectly. The ...
0
votes
2answers
124 views

Viewing a small pointcloud in QGIS 3.0 3D Viewer

I have a pointcloud in postgis, imported using pdal pipeline and chipper to 600 points per patch. I have the pointcloud (only 5000 points) displayed in QGIS. How do I get QGIS to render in in 3D in ...
-1
votes
2answers
93 views

Problem with the ST_Area in PostGIS [duplicate]

I have a question with a query in PostGIS. I try to calculate the area from a polygon. It actually works but the area-result is not correct. My Query is: SELECT name, ST_Area(way)/10000 AS hectares ...
2
votes
1answer
63 views

Is it possible to bring PostGIS database Query results into QGIS workspace other than using DB Manager?

I would like to use the QGIS modeller (or similar) to import SQL query results form my Database directly into my workspace. I am asking as part of my job is to repetitively add vector and DTM layers ...