Questions tagged [postgis]

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

Filter by
Sorted by
Tagged with
0
votes
1answer
23 views

How can I change the “NoData” value for a raster column in PostGIS?

I have a raster column with "NoData" value of 3.40282346638529e+038 in PostGIS. I want to change the NoData value to 100. I used the following code: SELECT ST_Reclass(ukc,1,'3....
0
votes
1answer
10 views

Compiling on CentOS fails due to ignored LIB flag

I need help with installing PostGIS. In general: I try to install it on a cluster (CentOS) and do not have admin rights. There is a sqlite3 installation (connected to the error), but this seems ...
1
vote
2answers
41 views

“ERROR: operator does not exist: geometry public.&& geometry” after changing PostGIS extension schema

I just moved PostGIS extension (I have version 2.5) from public schema to another (mob) schema by following the docs at this official PostGIS page. I did: ALTER DATABASE mob_odm SET search_path = ...
1
vote
1answer
24 views

Inserting GeoJSON in PostgreSQL using laravel controller

Actually.. I have GeoJSON that I send from javascript into controller. My GeoJSON: { "type": "FeatureCollection", "features": [{ "type": "...
2
votes
1answer
38 views

PostGIS slow ST_Intersection

I'm trying get get intersections between one straight line and some contour lines. Using the SQL below: DROP TABLE IF EXISTS v_line; SELECT st_geomfromtext('linestring(103.492217095662 30....
0
votes
0answers
15 views

Building PostGIS 3.0 and PostgreSQL 12 with confidence on Ubuntu 18.04?

I have failed in all previous attempts to build PostGIS on macOS, centos, or ubuntu in a manner that results in the creation of the address-standardizer and the reverse geocoder. I have tried all the '...
1
vote
1answer
40 views

Joining line segments in QGIS/PostGIS

I'm trying to join touching lines within a linestring dataset, so that I end up with a single feature for each set of touching features (eg the set outlined in green below). I've tried pretty much ...
2
votes
1answer
34 views

Union MVT features based on attribute / id in OpenLayers render

Following Paul Ramsey's article about ST_Subdivide I was able to split the Natural Earth boundaries dataset. Generating tiles which intersect the boundaries of Canada and Russia on the fly is now ...
1
vote
0answers
34 views

PostGIS rotating a line string to be vertical when viewed on screen

I'm building a Node.js application that will display Google Maps satellite images of golf course holes. I have 2 GPS points I'm pulling from a PostgreSQL database and I need to be able to take any ...
0
votes
1answer
56 views

PostGIS & Pagination

I have a pretty standard table with a geom column and a gist index on it. I would like to have a fast pagination on this table as it contains millions of rows. The query involves a ST_Intersects: ...
1
vote
0answers
39 views

Extending OSM database with new data

I want to add a new table inside my OSM database with extra data for every restaurant, and then when retrieving the places join the table by the "osm_id", is this possible and if yes, any ...
1
vote
0answers
34 views

PostGIS Raster layers not rendering in QGIS 3.14

I am having an issue with the rendering of PostGIS raster layers using QGIS 3.14 I have a PostGIS DB with multiple rasters and on QGIS 3.10 these are displaying as they should. When trying to open the ...
-6
votes
1answer
72 views

What are “degrees” in SRID 4326 and why can't they use meters? [closed]

I want to return all the houses for sale within 500 meters from my own house. I have this query: SELECT * FROM houses_for_sale WHERE postgis.ST_DWithin(coordinates, $1, 500); It returns houses ...
3
votes
1answer
70 views

Updating table from another table with function ST_CONTAINS in PostGIS

I tried to update an attribute 'solved' of a polygon table (table name: polygon) by using an attribute 'solved' of another point table (table name: point). What I want to do is that if ALL the points ...
2
votes
2answers
137 views

Merging two isolines into one in PostGIS

How can I sum two isolines A and B (closed linestrings with values) into one isoline C with summed values on common parts? For each line string there is a value and I would like to sum on common part. ...
0
votes
0answers
39 views

PostGIS, ST_Intersects & LIMIT performance question

I have a pretty standard table with one geometry column and a datetime column. Geom is indexed using gist and table is clustered + VACUUM ANALYZE. The Docker image is kartoza/postgis:12.0. I consider ...
2
votes
1answer
55 views

PostGIS validate the Z continuity

What is the solution to validate if a 3D linestring is always going down? All linestring nodes must have a lower value than the previous one, to check if Z values in a line are going down continuously ...
1
vote
0answers
31 views

QGIS Versioning plugin

I have created a database from QGIS Cloud free account. I have added a Post GIS layer into QGIS desktop. i have installed versioning plugin and added a group. After Selecting a Group when Hostorize ...
0
votes
0answers
13 views

Creating ensemble and aggregates from netCDF using PostGIS

I have downloaded climate forecast raster data in NetCDF 3.6 format for a series of models and time resolution of one day. The data is one file per model. Eventually, I would like to have an ensemble ...
1
vote
1answer
65 views

PostGIS query used to run very quickly on Postgres 9.6 / PostGIS 2.4, now extremely slow on Postgres 12.4 / PostGIS 3.0

We have two virtual servers on the same hardware (identical specs, 6 core Intel Xeon E5-2643), one is running Postgres 9.6 with PostGIS 2.4, and the other is running Postgres 12.4 with PostGIS 3.0. ...
0
votes
1answer
26 views

PostGIS ST_Area unexpectedly returns 0

Can someone help me understand why the following ST_Area query returns 0: SELECT ST_Area(ST_SetSRID(ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[-180.0,0],[180,...
0
votes
0answers
9 views

Performing spatial queries with the SODA API in Python

I am trying to fetch data from data.cityofnewyork.us with the Python client sodapy. It generally works as it should, but I just can't figure out how to perform spatial queries. For example, I would ...
0
votes
1answer
24 views

using ogr2ogr to import a shapefile into a PostgreSQL/PostGIS database [duplicate]

I am trying to load a shapefile into a PostgreSQL/PostGIS databases with the following statement run within the OSGeo4W shell ogr2ogr -f "PostgreSQL" PG:"host=localhost port=5432 dbname=...
4
votes
1answer
103 views

Finding the minimum spanning tree (MST)

I have a set of points at which I want to find the minimum spanning tree with PostGIS. I have no lines between them, I only have the starting point from which to start building the tree (the ...
4
votes
1answer
95 views

PostGIS speed up query on 9 million row polygon table

Table bl_data 9 million+ polygon rows with GIST index and the index is clustered create index ii3 on bl_data using gist(geom); CLUSTER ii3 ON bl_data; I have a query that finds records with the same ...
5
votes
1answer
100 views

When to use GiST and when to use SP-GiST index?

I'm aware of the documentation for both of the indexes, but can't decide which one to use. PostGIS documentation on these indexes Example: Let's say we have table data with the POINT attribute that ...
3
votes
2answers
92 views

How to add source in GeoJSON layer using OpenLayers

I am trying to add GeoJSON layer using OpenLayers which is in the PostgreSQL db. When I am running query as select ST_AsGeoJson(ST_Transform(geom,900913)) as farmcode from farmdatadd where areatbl = ...
2
votes
2answers
78 views

How to check two 3D Geometry are equal in PostGIS?

I am using following query for checking the given two 3D geometry are equal. The following two input geometry XY are same and Z is different, but query return true. Is any other method available for ...
2
votes
2answers
88 views

Polygon covered by the intersection of multiple linestrings (PostGIS)

I have a set a linestrings, and I would like to know the area covered by the intersection of those lines. I don't want to merge them though. To make things clearer, here is a diagram of three ...
1
vote
1answer
42 views

How to fix the problem of 'out of memory' in PostGIS Raster processes?

I am trying to combine a series of overlapping rasters to find out the most recent data. I think I may have 10,000 layers. When I run this query: CREATE TABLE most_recent AS ( SELECT ST_Union(rast,...
0
votes
1answer
64 views

PostGIS and GeoServer - alternative solution to serve combined spatial and time tables with high resolution data

This is the follow up of this other question of mine, although you don't really need to see it to understand the problem. I am trying to figure out what would be the best (maybe most usual) way of ...
0
votes
2answers
53 views

What is the proper convention for storing rasters in PostgreSQL database?

Just starting to implement PostgreSQL at work and want to make sure I'm building something that will be efficient down the road. Let's say I have a raster of surface temperature for every hour of ...
0
votes
1answer
36 views

Speeding up GeoServer response - splitting the main PostgreSQL table into multiple sub tables instead of using views would help?

I am sorry if the title might sound silly, but I will try to explain the problem and why that title seemed the right to me. I have an OpenLayers application which display different WMS Image Layers ...
2
votes
0answers
28 views

Which method is most suitable to change the pixeltype of the raster in PostGIS?

I have saved a raster in the PostGIS database and want to select it as PNG. However, ST_AsPNG only accepts rasters with pixel type 8 BUI or 16 BUI. But in this case it is a raster with pixel type 32 ...
0
votes
1answer
33 views

PostGIS intersection betwen polylines and buffer around points (polygons)

I have a PostGIS table of polylines and I need to intersect them with a table of polygons (conveniently buffers around points). In real world polylines are cables, and points are poles. I need to ...
4
votes
1answer
53 views

Transformation on insertion into PostGIS

If I have a specific SRID set on a geometry column in PostGIS, say 4326; if I then insert geometry with a different SRID, say 4283, what SRID will be stored in the database, will PostGIS transform the ...
1
vote
0answers
32 views

Is a SRID an attribute of geometry or geometry column? [closed]

I'm not an expert. Yesterday I found out that SRID in PostGIS is property of the column - an actual constraint that is even stored in pg_constraints table and then retrieved through geometry_columns ...
0
votes
0answers
11 views

Is there any way to return raster tiles from a bounding box with Django raster?

I've fully installed Django raster and have uploaded a GeoTIFF through the admin page. I'd like to allow the user to query the raster's tiles (stored in psql/PostGIS database) with bounding box values....
4
votes
1answer
101 views

Spatial interpolation in PostGIS without outputting Raster

I have some data that is similar to rainfall, ie. a latitude, longitude, and a Z number. It is in a point layer that is a nice clean point grid. Many Z numbers are missing in this grid. I want to ...
1
vote
1answer
43 views

Updating PostGIS layer with the result of intersection

I have a PostGIS layer which I would like to intersect with another layer (adjust its borders). The result of the intersection should then become the new PostGIS-layer (update the input layer). I ...
2
votes
1answer
43 views

Synchronizing on-prem PostgreSQL raster database with AWS RDS

I have a folder of rasters that updates daily that I load into a PostgreSQL database using the raster2pgsql command line tool. I want to make this database outward facing so that I can connect to it. ...
2
votes
3answers
42 views

Store Lon,Lat in generated column - PostgreSQL 12

I would like to store lon, lat as varchar in an PostgreSQL generated column that uses the_geom as data source. alter table poi add column lonlat varchar generated always as ( st_x(the_geom)|| ', '|| ...
0
votes
0answers
46 views

Time taken to build the routing network in the pgrouting

I am using: PostgreSQL 9.6 pgRouting 2.6.2 PostGIS 2.5 I have line geometry shapefile of count 700K. When I build the network using pgr_createTopology in the new DB, network created within 1 ...
2
votes
2answers
76 views

Group geometries of line data based on one common attribute and compute length

I have a vector file of river networks in QGIS. Some of the rivers are split up it multiple rows. What I'd like to do is to aggregate the rivers based on the attribute in one column (S_NAME) and ...
4
votes
1answer
144 views

Creating a new custom PostGIS function

I'd like to create a new, custom PostGIS function that would return a point geometry from a polygon that I can use for generating label points from a polygon, but I am not sure how to go about ...
0
votes
1answer
67 views

PostGIS. Find intersection of a polygon from 2 points

I have 2 polygons and 2 points stored in postgres tables and Im trying to find the front and back of one of the the polygons (green dots) based on a line from the 2 red dots. I'm trying to find the ...
2
votes
1answer
59 views

Zonal statistics (mode, variety) in PostGIS

I would need to get different statistics than are available in ST_SummaryStats. In particular I need mode (majority) and variety (number of different values) for a discrete raster and according ...
0
votes
0answers
61 views

Can't run pgRouting Layer plugin in QGIS 3.10.3

I was able to use osm2pgrouting tool successfully on Windows with PostgreSQL version 11.9 and pgRouting version 3.1.0. I successfully generated my "ways" file in a PostGIS conection in QGIS ...
1
vote
1answer
31 views

Exclude geometry column from properties of ST_AsGeoJSON output

The ST_AsGeoJSON function with the following query: SELECT ST_AsGeoJSON(t.*) FROM vw_ph t (ph, capture_datetime) LIMIT 1; Returns the following: { "type":"Feature", &...
2
votes
4answers
115 views

How to remove line overlap hierachically in PostGIS with ST_Difference?

I try to remove the overlap of line geometries in one table based on a class attribute. My table consists of two columns containing line geometries and a number "class" from 1 to 3. CREATE ...