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

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

3
votes
1answer
37 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 ...
1
vote
1answer
38 views

ST_Area yields total area encompased by a polygon. How do I get just the area covered by a polygon?

Here's my dataset: https://aaron-bauman.carto.com/dataset/curbs_1 I want to calculate the area covered by the purple only. ST_Area seems to be yielding all the area within the purple as well because ...
0
votes
1answer
20 views

Setting coordinate system in CARTO Builder map?

I uploaded a polygon layer in EPSG 25830, called 'parcels'. I know that the geometries are reprojected and the dataset has two geometry columns: 'the_geom' in EPSG 4326 'the_geom_webmercator' in ...
0
votes
0answers
17 views

Adding GeoJSON parameter on GeoServer SQL View?

Is it possible to add GeoJSON parameter on GeoServer SQL View? select * from data_geometry_table where st_intersects(geometry, ST_GeomFromGeoJSON('%geom%')::geography::geometry); And my geom value ...
0
votes
0answers
28 views

How to update “ST_StartPoint” as “ST_EndPoint” [on hold]

How to update ST_StartPoint as ST_EndPoint with some where conditions.
0
votes
0answers
25 views

Compiling PostGIS on RedHat?

I can't compile PostGIS on RedHat. I ran these lines. But when I run the "make" line it show me the next logs. Any ideas that how can I achieve this? tar xvzf postgis-2.4.5.tar.gz cd postgis-2.4.5 ....
2
votes
1answer
37 views

QGIS Draw dynamically marker's heading from field in a postgres database

General description: I am using QGIS as quick visualization tool, where I make a "PostGIS" database layer and I see user generated events. Table structure as follows: Id | time_of_event | ...
0
votes
1answer
17 views

OpenLayers 3, PostgreSQL, WFS: Extent jumps back to initial extent if zoom out too far

I'm working on an app using OpenLayers 3 with PostgreSQL/PostGIS tables and views, and an Esri basemap. The web app loads a set of features using an array of attributes from PostGres table (a view) ...
-1
votes
0answers
19 views

Transfer data from postgres database to SQL database using GDAL

I am new to GDAL and have been uploading CSV crime data to a postgis database using ogr2ogr. ogr2ogr -f "PostgreSQL" -lco GEOMETRY_NAME=geometry -lco FID=gid PG:"host=server user=user dbname=dbname ...
-1
votes
0answers
46 views

Why does it take a long time to copy features from a QGIS PostgreSQL layer and how to speed it up? [on hold]

So basically I have my QGIS project which loads a bunch of layers from PostgreSQL Problem is that sometimes users need to copy like 500 or 1000 features from a few of the bigger tables and in the ...
0
votes
1answer
24 views

Return rows after a spatial join where the count is zero

I am trying to determine the number points in a table of polygons AND include a row of zero for those instances where a polygon has no points in it. This query works well except that no zero rows are ...
0
votes
0answers
25 views

Re-sync OSM DB After Failure

I extract highways from OSM every day from a cron job and keep a postgis database up-to-date. Due to a database change, the syncing has stopped in December 2017. I am trying to re-sync with a sequence ...
1
vote
1answer
34 views

Osm2pgsql - Lines instead of polygons that represent buildings

So I am using a PostgreSQL database with the PostGIS extension to extract building data with addresses from osm data. I just discovered that in my dataset, imported with osm2pgsql of the german city ...
0
votes
1answer
12 views

Query to return closest LineString given a Point [duplicate]

I have a road lines data in a table with field roads(id SERIAL ,the_line geometry(LineString,4326)) suppose an accident occurs in a middle or at any location in that waterline i want to stop the ...
2
votes
1answer
53 views

Getting SRID Well Known Text (WKT) metadata from PostgreSQL?

The SRID metadata is showed by, e.g., this web page: Definition: Well Known Text (WKT) PROJCS["UTM Zone 24, Northern Hemisphere", GEOGCS["GRS 1980(IUGG, 1980)", DATUM["...
1
vote
1answer
21 views

Importing PostGIS raster in GeoServer

I'm trying to import a Sentinel 2 PostGIS raster in GeoServer. Here are the steps I've taken so far: First I transformed the JPEG2000 files to GeoTIFF via gdal_translate -of GTIFF S2A_[...]_B02.jp2 ...
1
vote
1answer
47 views

ST_DWithin query throwing 42601 error?

I am trying to get from a particular point more informations about the surroundings (which are also points). So I wrote this: SELECT * FROM osm_point WHERE ST_DWithin(way,(ST_SetSRID(...
0
votes
1answer
24 views

SQL and CARTO - Dot Density Visualization

I need help with running a SQL on CARTO. I want to generate a dot density similar to the link here. The second portion of the SQL code deletes the cartodb column and nothing changes on the map. ...
1
vote
1answer
47 views

Using multiple St_Dwithin clause result in a huge performance loss

In PostgreSQL using PostGIS, I want to find every point which are close enough of some other points but which also are far enough from another points series. So here is my query : SELECT pod.id, ...
0
votes
1answer
21 views

Sequenced path using pgrouting (going through the POI of the user)

I'm using the pgr_astar function of Postgres and I want to personalize the optimal path proposed according to the preferences of the user. For example if the pg_astar propose the red path, I want to ...
3
votes
0answers
30 views

Generating a neighborhood pixel raster from existing raster using PostGIS

I am starting with a PostGIS raster table containing anywhere from 6-12 bands. In a nutshell, the goal is to take each of these bands, perform a 3x3 neighborhood average on each pixel in the band, ...
2
votes
0answers
21 views

ST_ApproximateMedialAxis producing strange outputs

My problem is as follows: I have some lines (yellow), that I want to merge in one line if they are at a certain distance (blue, buffer), and after a long research I found this function from PostGIS/...
3
votes
1answer
23 views

Displaying lines in QGIS from ST_MakeLine() not working with UPDATE, but works with CREATE TABLE

When using UPDATE to create geometries based on the SCHNUM (school number) of students to their attend school, not all the lines show up in QGIS. However, when the basic query result is written to a ...
2
votes
0answers
41 views

Geography point and integer in multicolumn PostgreSQL index

I want to create a multicolumn spatial index on a geography point and integer in order to do efficient spatial queries. The integer is a user_id and queries are always limited to a single user. Is a ...
1
vote
1answer
25 views

How to write dynamic SQL for a function that has dynamic SQL parameter?

I am trying to execute a string SQL query in function i.e. Create or Replace Function Route(edge_table text) Returns table(sequence integer) as $$ Begin Return Query Execute 'with r as( SELECT seq, ...
0
votes
1answer
39 views

Where can I find correct shapefile for Montenegro municipalities?

I downloaded a shapefile for Montenegro from https://gadm.org/download_country_v3.html and created a table in PostGIS using shp2pgsql. The table has column geom of type geometry which is a ...
0
votes
1answer
33 views

'UPDATE 0' results for ST_Within query - locating points within polygons

tldr; I cannot get ST_Within to find business points that fall within two sets of polygons. I keep getting zero results. All three tables have geom col with SRID 4326. I have a data table where I ...
1
vote
2answers
47 views

Trigger function PostgreSQL/PostGIS points within polygon

I wan't to update a column in PostgreSQL that counts the number of points inside a polygon using a trigger function. The function should be fired on each insert or update of the polygons or the points ...
0
votes
0answers
23 views

Automating Redshift data dump to a PostgreSQL DB every 12 hours

I have a redshift database that changes multiple times a day. I want to use this "live" data to make web maps and I have a Postgres DB where all my web-mapping data is stored. I want to continuously ...
0
votes
0answers
14 views

GEOSERVER: Implement generic select all layers that defines a param = 'somevalue'

i have a layer that requires users to insert a value (normally an id of sorts) to retrieve information about a particular item. Something like in the SQL view on geoserver select * from table k where ...
3
votes
0answers
50 views

How do I select the longest connected lines from PostGIS ST_ApproximateMedialAxis?

I used PostGIS ST_ApproximateMedialAxis to transform polygons representing mountain ranges into a series of lines. Now, I'd like to select only the "longest" line segment for each group, so that I ...
1
vote
0answers
28 views

Best way to relate PostgreSQL table to layer [closed]

There is an online PostgreSQL database (non-spatial) with water monitoring data which is regularly being updated, in another side is a spatial layer of rivers (for example) which should be represented ...
0
votes
0answers
25 views

ArcGIS & PostGIS Error

When I export a ShapeFile to PostgreSQL from ArcGIS it shows the error: None of the layers to be read contain FEATURE IN THE SELECTION SET. Therefore all the features in the layer will be read. ...
3
votes
1answer
72 views

PostGIS trigger does not work as expected while checking intersections

I'm trying to write a trigger that should prevent saving lines which do not end at building boundaries. ( A PostGIS-Solution for something like the topology-rule "Must be covered by boundary of" in ...
7
votes
1answer
141 views

Can LINESTRING “contain” POINT?

According to Wikipedia, The predicates Contains and Within have subtle aspects to their definition which are contrary to intuition. For example,[10] a line L which is completely contained in the ...
1
vote
0answers
31 views

Is there a function to obfuscate a PostGIS point or geometry?

I am looking for a function, perhaps a Perlin noise implementation or something similar, for PostgreSQL/PostGIS. Does such a thing exist in core PostGIS? If not, does such a function exist an ...
0
votes
1answer
27 views

Mapserver Point Layer

I am able to draw and create wms mapfile for POLYGON and LINE but not for a POINT, I am not sure what maybe the issue, can you assist LAYER NAME Country TYPE POINT EXTENT -25 -35 51 37 ...
1
vote
1answer
33 views

PostGIS Shapefile Loader Tool / export manager

I am trying to upload a shapefile to PostGIS using the PostGIS Shapefile Loader Tool/export manager and I receive this error? Connection to the PostQL seems successful... though. Any idea? ...
0
votes
0answers
28 views

Breaking LineString into MultilineString group by distance between two points and it's time

This is line string of my geometry column that created by postgis and shown in qgis: This is multiline string like above: I am new on gis and How can i do break line and convert it into multiline? I ...
0
votes
1answer
43 views

Convert lineString to MultilineString in PostGIS

How can I convert the line string to multiline string in PostGIS? I have converted geometry points to line string by this: st_makeline(location.shape) but by this command: SELECT uuid_generate_v4() ...
0
votes
1answer
44 views

Why should I use PostGIS functions? [duplicate]

Is there a reason for using PostGIS functions such as ST_Area and ST_Length when there are already functions/operators for doing the same thing in PostgreSQL (area and @-@)
0
votes
2answers
31 views

PostGIS: Change raster values depending on values from other raster

I have two rasters and I want to change all the values from "raster1" to 4, where they are larger than the values from "raster2" and to 0, where they are smaller. So raster2 has only one value, which ...
0
votes
1answer
48 views

How to convert SRID's on the fly and via command line, on INSERTION's query in PostGIS?

We are working on a project that used shapefiles in order to build our first PostGIS database, through a tool called “PostGIS Shapefile Import/Export Manager”. The problem is that now we need to ...
0
votes
0answers
17 views

How can I upload .HDF.nc and .nc4 raster files to PostGIS?

I want to import several raster layers (.HDF.nc and .nc4 files) into PostGIS. This is what I tried so far: I created a PostGIS Database in pgAdmin4 after installing PostgreSQL 10 with the PostGIS ...
1
vote
0answers
50 views

Export PostGIS geom polygon to PNG

I am trying to build a REST API using Flask and Postgis. I have a PostGIS table with a GEOM column. Querying the table using ST_AsGeoJSON gives me a MULTIPOLYGON with lat, lon in WGS84. [('...
1
vote
1answer
48 views

Cache lookup failed for function 1 while enabling postgis on postgres 10

I am trying to enable PostGIS on a PostgreSQL 10 database on Windows. Earlier it used to work fine, but now I am getting an error 'cache lookup failed for function 1' on enabling the extension. I am ...
0
votes
1answer
68 views

ST_Area and SRID 4326

I'm working in the SRID 4326 system and need to compute the area of rectilinear polygons. Our geometry (geom) column is in WKB format When I execute the ST_Area function it appears my results are ...
0
votes
1answer
48 views

Optimising a PostGIS table

I've been uploading a huge amount of contour information to a PostGIS / PostGres table. Uploaded using QGIS' db manager to a separate table a chunk at a time, and then using a quick bit of SQL to ...
1
vote
2answers
41 views

ST_Union doesn't completely dissolve boundaries of adjacent polygons with CurvePolygon

Starting from this set of geometries: I need to achieve this result (from ArcMap's Dissolve algorithm): But what I get from PostGIS is this: Here is the query I use: SELECT (ROW_NUMBER() OVER())::...
1
vote
0answers
66 views

Error Quantifying Land Cover Classes by Polygon in PostGIS

I am trying to ultimately calculate proportion of land cover types by polygon in PostGIS, so starting using st_valuecount to get counts of pixels with different land cover types. The data are pretty ...