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
2 answers
37 views

Clip data using GeoPandas or PostGIS

I have two geodataframes - points. First gdf contains smaller number of points. I am trying to clip my second gdf (with higher number of points and therefore bigger area) to the extent of first gdf. ...
user avatar
2 votes
0 answers
46 views

Maritime routing considering high detail depth data

I'm looking for ways to find routes between points considering high detail depth data. In the screenshot I presented here the size of the depth polygons can be down to a few square metres. My overall ...
user avatar
3 votes
0 answers
93 views

Improving performance of spatial intersection and aggregation query

Edit: Per a comment, here is some more context and the basics of what I'm looking to do, that I think would better enable someone to offer input from a first principles perspective. Its a lot of ...
user avatar
  • 184
1 vote
1 answer
44 views

The function with WITH is throwing an error in PostgreSQL

I am trying to put some statements into the following function. CREATE OR REPLACE FUNCTION public.raster_data_extraction( resolution integer, pixel_size double precision, upper_x double ...
user avatar
1 vote
0 answers
33 views

Area of two intersecting polygons on PostGIS

I am trying to calculate the area of two intersecting polygons using PostGIS: I've found this solutin: SELECT layer.text, layer_2.text, sum(st_area(st_Intersection(layer.shape, layer_2....
user avatar
2 votes
1 answer
23 views

Using St_expand and St_Squaregrid work together in PostGIS

Using this query (which works), I am able to create a square grid. select ST_asGeoJSON(geom) from ( select ST_SquareGrid( 0.0001, ST_Transform( ...
user avatar
  • 123
0 votes
0 answers
10 views

Error when trying synchronize from Qfield

I am get error when trying synchronyze information from QField to Postgis table: The error in the QGIS console is following: Please help me because the fields are well defined in the database and I ...
user avatar
2 votes
0 answers
32 views

ST_TRANSFORM NAD27 to WGS84

I've encountered an issue when transforming NAD27 coordinates to WGS84. The resulting WGS84 coordinates are off by quite a bit (10-12 feet in most cases) and it lead me to digging into why that is so. ...
user avatar
-1 votes
0 answers
26 views

Seeking web application options for PostGIS filtering

As of today I have a GeoServer connected to my PostGIS database with preset SQL filters. I have a website which lets users select different filters and when they press the "Run" button it ...
user avatar
  • 61
1 vote
1 answer
101 views

Creating an irregular point grid with provided row and column numbers in PostGIS

I am looking to create an irregular grid of points via PostGIS, using the four provided corner (point) coordinates, and number of point-rows, and point-columns. Four corners may or may not be ...
user avatar
0 votes
1 answer
30 views

Multiple Geometries geopandas.to_postgis Return "Geometry SRID (0) does not match column SRID (4326)" Azure Postgres Version 11

I am trying to import a GeoDataFrame to PostGIS using the "to_postgis" method. I have multiple geometry columns. It works in my local machine and docker with Postgres version 14 and PostGIS ...
user avatar
0 votes
0 answers
41 views

Store created features to PostGIS table via GeoServer does not work: ERROR: Refused to set unsafe header "User-Agent"

I'am trying to develop a crowdmapping application for setting points via an OpenLayers Client. These have to be stored via GeoServer to a PostgreSQL database with PostGIS extension. When I try to save ...
user avatar
1 vote
1 answer
34 views

Reproject valid features returns geomety error with PostGIS

PostGIS3.2, PostgreSQL 13 I have a table with multipolygons. There is a constraint CHECK (st_isvalid(geom)) on it. Run a query with st_ivalid(geom) returns no error fortunately ! When I try to ...
user avatar
  • 1,065
1 vote
2 answers
31 views

Substrat values from two polygon tables according to maximum polygon overlap using Postgis

I have two polygon layers/tables in QGIS. The two layers represent (almost) the same spatial division (census boundaries). Basically, the polygons don't perfectly overlap because of differences in ...
user avatar
  • 153
3 votes
1 answer
46 views

Create a view in a schema in QGIS DB Manager

When I have created a query in the DBManager window of QGIS I want to save it as a view. When I click on the Create a view button, I'm then prompted for a name. This is all fine, but I want to create ...
user avatar
  • 75.5k
1 vote
0 answers
55 views

Efficiently fusing (or merging/combining) two points geometry data sets

I haven't found good suggestions while googling "GIS fusion of two points geometry data sets" so I am here to seek suggestions for my data fusion problem. I have two points table in my ...
user avatar
2 votes
1 answer
45 views

Extracting non-overlapping polygons in PostGIS

I am trying to perform what I thought ought to be a simple operation in PostGIS. I would like to use a set of circle polygons to split a set of square polygons and extract the resulting non-...
user avatar
  • 185
1 vote
1 answer
26 views

How PostGIS stores COG ranges?

PostGIS can use out-db rasters, stored as Cloud Optimised GeoTIFF (COG) in external storage, like S3 or GCS. PostGIS will use HTTP Range Requests to fetch parts of the TIFF. How does PostGIS know ...
user avatar
  • 113
0 votes
0 answers
28 views

How to join three tables with intersection

I need to join three tables with intersections. I have three tables: Raster data (Sentinel 2 imagery); Grid table 1.28 x 1.28 km; OSM landuse table/ Based on the grid I need to extract band values ...
user avatar
6 votes
2 answers
206 views

Splitting lines by multiple polygons, taking length of particular segment, using PostGIS

I have a table of 100k+ non-overlapping POLYGON features, and a table of 50k+ simple LINESTRING features (each line has only 2 vertices). The below is a common arrangement of these features on a map, ...
user avatar
  • 1,497
0 votes
1 answer
41 views

Retrieving raster data is taking forever

I am trying to execute queries through Python. Unfortunately, it takes forever. In fact, executing the same query in the DBeaver takes around 5 secs. In addition, queries such as create table, and ...
user avatar
2 votes
1 answer
129 views

How to snap points to line

I am having a table lines being MULTILINESTRING (2 Dimensions) and a table points being MULTIPOINT (3 Dimensions). The two are in the same CRS. Now, I am trying to snap all the points to the closest ...
user avatar
  • 3,091
1 vote
0 answers
22 views

Snapping points to lines returns unexpected results [duplicate]

I have a linenetwork lines and a lot of points points. The table lines has 42869 rows, the table points has 11264 rows. I am trying to snap each point to its closes line. SELECT p.id, ...
user avatar
  • 3,091
2 votes
0 answers
42 views

Storing encoded polyline using PostGIS

I'm entirely new to PostGIS and trying to store encoded_polylines to test some proximity searches with lat/long points. So, I created a simple table composed of a pkid, an encoded polyline, and a ...
user avatar
4 votes
1 answer
94 views

Locate ways (multilinestring) that intersect points

I have two tables: crimes (points/ST_MakePoint) and ways (multilinestring). Initially what I did was reposition the points to the point closest to the track using the following: UPDATE renzagoras....
user avatar
2 votes
1 answer
19 views

Clip and export PostGIS polygon to GeoTIFF

I have a Multipolygon layer in a PostGIS database which essentially makes a gridded data field. I want to clip this data using a bounding box and the clipped data should include all the polygons with ...
user avatar
  • 21
0 votes
0 answers
33 views

ST_Contains not returning a correct result

I'm trying to do a spatial join between a table that stores locations of photos and the table with names and geometries of countries and the table containing locations of major cities. For example ...
user avatar
  • 109
-1 votes
0 answers
10 views

What is the best way to associate pixel coordinates in a raster with values in another raster?

Looking at a NetCDF file that contains a subdatasets for the variables temp, lat, lng and a couple others Temperature (4 dimensions) depth (20) time (240 time steps) x (38 pixels) y (53 pixels) Lat (...
user avatar
0 votes
1 answer
34 views

PostgreSQL command takes 11 hours after update from version 10 to 13. PostGIS update from 2.5.1 to 3.2.0

Normally the following SQL Command takes a couple of minutes: SELECT AX14004X11003_ID, polygons.objid, points.objid from polygons join points on ST_Touches(points.shape, ...
user avatar
  • 2,337
1 vote
2 answers
42 views

Water Pipeline analysis in PostGIS like Pipe Burst analysis, valves ON/OFF affected areas etc

I have made a network of Water Pipelines with valves of my town, now I want to carry out certain analyses like Pipeline Burst analysis to identify the least number of valves that needed to be turned ...
user avatar
  • 11
0 votes
1 answer
35 views

Can't call PostGIS ST_Simplify

How can I call PostGIS's ST_Simplify function? postgres=# SELECT ST_Simplify( geom, 0.1 ) FROM mains; ERROR: function st_simplify(geography, numeric) does not exist LINE 1: SELECT ST_Simplify( geom, ...
user avatar
1 vote
0 answers
19 views

Upgrading PostGIS on Mac

I am trying to upgrade PostGIS from 3.1.3 to 3.2.1. It is on MacOS Monterey. Followed the instructions given here run on terminal brew install postgis On pgadmin ALTER EXTENSION postgis UPDATE; ...
user avatar
  • 579
0 votes
0 answers
41 views

Publishing raster from PostGIS with GeoServer

Goal: Publish rasters stored in columns of PostGIS DB using GeoServer GeoServer-version is 2.19.0 run inside a docker-container I found the Image Mosaic JDBC-Extension and followed this post: ...
user avatar
  • 157
1 vote
0 answers
69 views

What is the difference between pg_tileserv and Martin?

I'm looking for a way to display vector data in PostGIS on a map. Initially I was going to setup a GeoServer to do this via WMS, but have since seen that PostGIS allows for creating vector tiles ...
user avatar
1 vote
1 answer
46 views

Reconstructing PostGIS geometry from ST_DumpPoints()?

The PostGIS ST_DumpPoints function seems to have enough data in the return value about the structure of geometries (in the path field): It returns a set of geometry_dump rows, each containing a ...
user avatar
  • 10.3k
2 votes
1 answer
28 views

Inverse of ST_RasterToWorldCoord() in PostGIS?

Is there an inverse function of ST_RasterToWorldCoord() in PostGIS (or the underlying GEOS/gdal), which, given the coordinates of a point in the raster's SRS, returns its row and column number? I ...
user avatar
  • 10.3k
2 votes
1 answer
68 views

Create heatmap and output to raster or shapefile

I am trying to create heatmap with spatial data to display on GeoServer. Please note that it should be smooth by some kernel density estimations and not a simple count. Looks like the geoplot.kdeplot ...
user avatar
  • 579
1 vote
1 answer
24 views

Inserting data and point into a table PostGIS / Error: parse

I'm trying to import in a table with columns (name, x, y, geom) like this: INSERT INTO public.table (name, x, y, geom) VALUES ('name', X, Y, ST_GEOMFROMTEXT('POINT(X,Y)', SRC)) But I'm obtaining this ...
user avatar
  • 11
2 votes
2 answers
65 views

PostGIS Splitting complex linestring over dateline

I have complex linestrings in PostgreSQL (see sample below). Many of these linestrings will cross the international date line (longitude 180 / longitude -180). The problem is that PostGIS assumes ...
user avatar
0 votes
0 answers
34 views

ST_DWithin with variable distance in PostGIS

I have a table of two columns: Polylines (Linestrings) geometry coordinates. Buffers: distance in kilometers. The table contains about 1000 entries. Using PostGIS "ST_DWithin" or any ...
user avatar
1 vote
0 answers
30 views

How to speed up the extraction of pixel values by grid?

I have a grid with a size of 2.24 x 2.24 km with 224 rows and columns making 10 m for each cell. Now I want to overlap this grid with my raster data which is 109 x 109 km and extract pixel values (all ...
user avatar
1 vote
1 answer
20 views

Trying to cast box2d to geometry gives back only numbers

I have a query where I use select ST_Extent(geometries) which returns a box2d object. I would like to cast it back to a geometry object since that is what I need but I was unable to do so with select ...
user avatar
1 vote
0 answers
29 views

How can I get the raster cell values for a set of rasters in PostGIS, for each cell that intersects an arbitrary, user-provided polygon?

In PostGIS, I have a set of (large, U.S.-wide) rasters at high (30m) resolution. For my application, I need to take an arbitrary area of interest provided by the user, likely ranging in size from ...
user avatar
3 votes
2 answers
49 views

How get list of id's of intersecting of polygons in the same table by PostGIS?

I have a table looking this this id geom 1 POLYON.... 2 POLYON.... 3 POLYON.... 4 POLYON.... 5 POLYON.... 6 POLYON.... .... 300 POLYON.... Some (not all) of them ...
user avatar
  • 3,091
1 vote
1 answer
34 views

How to determine the intersection of a Raster and a point Shapefile? [closed]

I am new to PostGIS and I can't find a way to intersect my raster map with my points shapefile. The raster is the country and the shapefile is associated with the cities. I would like to determine the ...
user avatar
  • 29
0 votes
1 answer
24 views

Shapefile to PostGIS to GeoJSON - co-ordinates

I'm trying to do what must be a very basic and common operation: load a Shapefile into PostGIS, and then search within a bounding box, returning GeoJSON. Putting the Shapefile in like this, specifying ...
user avatar
0 votes
0 answers
24 views

How to extract pixel values from raster?

I have created a grid using fishnet which has cells that are equal to 10 m (with and height) and the grid size is 2.24 x 2.24 km. Using this grid I would like to extract all band values from the ...
user avatar
0 votes
0 answers
30 views

QField error to sync data to QGIS

I have a form to collect data in QField. The configurations in the QGIS project are: The relations The tables in the database The form QGIS to collect data in field In QGIS the form works perfectly,...
user avatar
0 votes
1 answer
40 views

PostgreSQL doesn't update itself automatically with the changes in my original shapefile

I have two shapefiles and I push them into my PostgreSQL via PostGIS Bundle 3 for PostgreSQL and it works fine. But when I delete a feature in the same shapefile imported again as PostGIS in my QGIS ...
user avatar
2 votes
1 answer
53 views

Classifying lines based on number of points located on them using PostGIS [closed]

I have a lines layer (roads) and a points layer (accidents). I'm projecting the points on the lines with st_closestpoint so that I'm sure the accidents are located on the closest road line. I now want ...
user avatar

1
2 3 4 5
175