Questions tagged [postgis]
PostGIS is an extension for the PostgreSQL object-relational database that adds support for geographic objects.
7,715
questions
2
votes
2answers
33 views
Intersecting line at junction in PostgreSQL
I have roads of a country having 899371 features, which were in shapefile and I had imported to PostGIS. I am trying to intersect line at every junction. I have used following commands:
1st command:
...
1
vote
0answers
38 views
Why does PSQL ignore spatial index in “where NOT ST_Contains”, but uses in “where ST_Contains”?
I'm creating a new table of trips in my PostgreSQL/PostGIS database, where trips are comprised of a series of individually identified points, based on where the points within each trip are. The idea ...
1
vote
0answers
24 views
ST_AsPNG query has wrong position on Mapbox
I am trying to import GeoTIFF files into a PostgreSQL database using raster2pgsql.
I use the following query to import the data into my database:
raster2pgsql -s 4326 -c -P -I -C -t 256x256 *.tif ...
2
votes
1answer
44 views
Is it possible to do raster calculations with ST_Union (PostGIS)?
I would like to perform raster calculations in PostGIS. (A change detection should be performed, for this purpose every pixel of different raster is accessed and it is checked if the pixel values have ...
2
votes
0answers
36 views
Publishing PostGIS raster vs publish file raster in GeoServer; what's best regarding performance? [closed]
I want to show high resolution raster layers in a web app (Open Layers) using GeoServer and I need to know what option is best in terms of performance.
My ideas are:
Publishing the raster layer as a ...
1
vote
1answer
37 views
Creating a Bounding box on pgr_Dijkstra to make it faster
I have a problem with the Dijkstra algorithm in regards to the query speed.
My current php script for the query is looking like this:
$sql = "SELECT * FROM pgr_dijkstra(
'SELECT id, ...
0
votes
0answers
8 views
How to fix Cannot create PoolableConnectionFactory (FATAL: password authentication failed for user ) [duplicate]
I am working on project using GeoServer, PostgreSQL and docker. I am newbie in all these technologies.
I publish new layer in GeoServer and when I try to preview, my browser tries to download instead ...
0
votes
0answers
21 views
qgis GPKG GDAL Export to PostgreSQL (available connections): Unable to open datasource with the following drivers
In a effort to convert some geopackages to a (some) postgis table(s) on our test postgresql server. To make some querry's we use the GDAL tool Export to PostGreSQL IN QGIS. However we get an unable to ...
0
votes
0answers
99 views
Connection Error: Add PostGIS Database on Geoserver through Docker on Geonode:
I am using GeoNode with connection to Geoserver through Docker.(from here: https://github.com/GeoNode/geonode).
I am now trying to connect to a PostGIS database. I have indicated the host ip address ...
1
vote
3answers
60 views
Finding line segment of point on linestring using PostGIS
Is there an easy way to retrieve the line segment of a point on a linestring using PostGIS?
To map a point onto a linestring, I use ST_(3D)ClosestPoint. Now I would like to find the index of the line ...
1
vote
2answers
98 views
PostGIS: create new polygons in between existing?
PostgreSQL: I have a PostGIS multipolygon table containing millions of features. It covers 95% of the country, however there are gaps between individual polygons. How can I fill these gaps with new ...
1
vote
0answers
66 views
QGIS: Error when using Undo/Redo on layers loaded from PostgreSQL database
I am working with layers loaded from PostgreSQL(11.7)/PostGIS database to QGIS 3.10.7. When I try to undo/redo changes made on the layer it fails, sometimes during the undo and basically every time ...
0
votes
2answers
22 views
Loading shapefile into PostGIS: ${TRAINING_ROOT} Directory
I try to follow this tutorial (link below: Loading shp file into PostGIS), however I dont understand how do I get to the $TRAINING_ROOT directory? It always tells me: No such file or directory.
https:/...
1
vote
1answer
140 views
Mapbox GL JS: Displaying polygons from different coordinate systems / projections
I'm new to GIS and have access to OS MasterMap Topography Layer data (UK).
I've got it loaded into PostGIS and am querying the polygons that I'm interested in (buildings). My understanding is the ...
4
votes
2answers
418 views
Fitting rectangle with maximum area inside polygon in PostGIS
I am looking to fit a rectangle inside a polygon which would have the maximum area for the rectangle.
I tried using ST_OrientedEnvelope(geom) but this would give the rectangle fitted outside the ...
0
votes
1answer
28 views
Show number of added satellite images in PostGIS
I want to check how many bands of a satellite image have been added to a PostGIS raster table.
I know that you can get information by \d raster_columns (as told here, but it seems as this gives only ...
0
votes
0answers
15 views
An error while loading a KML file from distance and area measurement application unto the QGIS
I am trying to load a KML file from distance and area measurement application onto QGIS. The problem I encounter is that I receive an error saying:
Invalid Data Source:C:\Users\user003\Desktop\files\...
3
votes
1answer
62 views
Get best fitting rectangle polygon in PostGIS
There is a polygon and i want to get the best fitting rectangle.
POLYGON((530298.250279478 162222.258455872,530298.231436834 162222.252144356,530291.717023011 162220.760892999,530291.4 162222.2,530307....
3
votes
1answer
72 views
Grade separation routing using pgRouting
I am using pgRouting version 2.6.
I am creating a routing topology from a subset of lion data https://www1.nyc.gov/site/planning/data-maps/open-data/dwn-lion.page
I have gone through the process of ...
1
vote
0answers
30 views
Unwanted lines in Geoserver layer [closed]
I recently upgraded my Geoserver from 2.1.1 to 2.17.1
Since I have done that, one of my layer show unwanted lines in his polygones :
This is what is show by a GetMap WMS request on the layer
The ...
1
vote
0answers
35 views
How to make a PostGIS LINESTRING with only one initial point
I have a service that will be sending GPS coordinates as it moves, and these will be added to my PostgreSQL / PostGIS database.
Right now, created a table with one column that is a Geometry("...
1
vote
1answer
29 views
How to draw a marker with Mapnik?
After setting up a tile server accordingly this instruction https://switch2osm.org/serving-tiles/manually-building-a-tile-server-20-04-lts/ my offline tile server working nicely.
And after helping of ...
0
votes
1answer
59 views
Getting meters from pgr_dijkstra
I imported http://download.geofabrik.de/europe/germany/baden-wuerttemberg.html into my PostgreSQL database which supports PostGIS.
Now I wrote a PHP script which gets two points (lon, lat, which are ...
0
votes
0answers
21 views
Is it possible to create a geometry from a proj4 string and Cartesian coordinates in PostGIS?
Considering I have a proj4 string such as:
+proj=tmerc +lat_0=12 +lon_0=-12 +k=1 +x_0=0 +y_0=0 +datum=WGS84 +units=m
Is it possible to create a geometry given X and Y offset (in meters)? For example ...
0
votes
1answer
44 views
Geoserver parameterized SQL View layer vs. SQL View layer with WMS time dimension. Any differences in speed?
I have two PostGIS tables, cities (city_code and geometry column) and precip (city_code, date, precip). The precip table can have more than one entry for each city since I have a precipitarion time ...
-1
votes
1answer
67 views
How can I insert a satellite image with multiple bands into PostGIS?
I am trying to insert a satellite image (e.g. a Sentinel Image) into PostGIS. What is the best structure to save the different bands?
I know that you can use somehow use 'ST_AddBand' or 'ST_Band', but ...
1
vote
1answer
24 views
Check if a point is contained by any polygon stored in PostGis
I'm using PostGIS and I've created a table:
CREATE TABLE test (id SERIAL PRIMARY KEY, poly geometry(POLYGON));
Inserted a polygon:
INSERT INTO test(poly) VALUES(ST_GeomFromEWKT('SRID=4326;POLYGON((0....
3
votes
1answer
34 views
How to create a map to PNG file with specific box coordinates by Mapnik?
I recently manually built a tile server with this instruction ->
https://switch2osm.org/serving-tiles/manually-building-a-tile-server-20-04-lts/.
I downloaded location data for Russia from ...
2
votes
1answer
68 views
Group geometry points and create a linestring for each group
I have a table of points at a Postgres database that have two main columns (geom geometry, time timestamp).
I need a query to retrieve as linestrings or as array of the geometries the points that are ...
0
votes
0answers
23 views
Create point in the origin of a CRS using PROJ.4
I know we can use ST_TRANSFORM to transform a geometry into a different spatial reference system, which can be defined as a PROJ.4 string such as: +proj=gnom +ellps=WGS84 +lat_0=52 +lon_0=0 +no_defs. ...
1
vote
1answer
45 views
Get all geom points that are more than 3 meters from the linestring, at big scale
With the help of Osmosis i was able to import all the data of a Open Street Map city, (Nodes, ways, etc...).
In another table at same db, i have Millions locations in that city, these locations are ...
0
votes
1answer
21 views
ST_Union on Rasters in Geoalchemy2 using ORM
I am attempting to perform a union on two rasters I pick from my postgis database using GeoAlchemy2.
I have a Raster table defined:
from geoalchemy2 import Raster
from sqlalchemy.sql import func
Base ...
1
vote
1answer
42 views
How to do small expansion into LINESTRING ends?
The geometric problem is similar to this other one, that can be described as "small extrapolation of the line ends".
The correct solution must to expand/extrapolate d meters (ex. 2 meters) ...
6
votes
1answer
175 views
PostGIS ST_Intersects does not return expected result
I want to search objects that have at least one point inside of a rectangle. Based on the size of the rectangle, PostGIS sometimes returns the contained object, sometimes not.
create table t1 (id ...
-2
votes
1answer
59 views
it is possible do this on PostGIS? [closed]
it is possible do this on PostGIS?
WKT from this geom:
'LineString (-103.38799494051174577 21.84240787106980974, -103.18255979902365027 21.95852338582395546, -102.94586278817865832 21....
3
votes
0answers
40 views
Getting the results of a PostGIS query to a file with ogr2ogr with low memory usage
I have a PostGIS query returning geographies and attributes. The result set is deterministic and ordered (so OFFSET and LIMIT work for getting subsets).
The result set of my query would exceed my RAM ...
5
votes
1answer
353 views
Is the Geometry(4326) an alias for Geography datatype?
The main reference about Geography datatype seems docs/using_postgis_dbmanagement:
Prior to PostGIS 2.2, the geography type only supported WGS 84 long lat (SRID:4326). For PostGIS 2.2 and above, any ...
1
vote
1answer
89 views
Problems enabling the postgis_raster extension, postgis_raster.control seems to be missing
I am a student and to learn more about PostGIS. I installed it to my Raspberry Pi, so that I can remotely access it via my laptop. The installation worked with the exception of the raster module.
When ...
0
votes
1answer
32 views
Difference in area size calculation since update packages
On my mac I use brew to install PostGIS and all dependencies, including geos.
Since month or so, I have a failing test in my Python code.
A polygon that is entered in WGS84 (srid 4326), the converted ...
3
votes
3answers
310 views
Error transforming geometry into lat-long coordinates
I'm trying to transform some geometries from the postgis database into lat long coordinates:
This is the query:
SELECT st_x (geometry) as lng, st_y (geometry) as lat FROM final
and this is the error ...
3
votes
3answers
101 views
Getting ST_IsSimple reason and detail similar to ST_IsValidDetail in PostGIS?
How can you get ST_IsSimple reason and detail similar to ST_IsValidDetail in PostGIS?
When validating geometries I can use these handy functions to not only check if geometry is valid, but also the ...
0
votes
0answers
75 views
Connecting to Cloud SQL database in Colab runtime
I am very new to GCS and Cloud SQL, so forgive me if I am not asking this question well.
A colleague and I have been working on a machine learning workflow that generates polygons from some ...
1
vote
0answers
49 views
Combine rasters in PostGIS with algebra operations
I have 2 raster tables with one band. What I want to do is clip the rasters with a polygon and multiply pixel values with different values and combine those multiplied rasters into a new one. The main ...
2
votes
0answers
43 views
How to save each band in a new row with it's name, in PostGIS?
I have used PostGIS's raster2pgsql to import my multi-band raster data into my PostGIS database. Here is the code that can do this operation:
raster2pgsql -I -C -e -Y -F /home/jj_user/input.tif ...
1
vote
2answers
53 views
How to manage different SRID data in same database table? [closed]
My data is in two different cities and three different EPSG code area. (Database is PostgreSQL/PostGIS.)
So I think, I can not set SRID values by data. Making 3 different table is difficult. How can ...
3
votes
1answer
63 views
Get dimension of rectangular polygon PostGIS
Is there a way to calculate the width and height of a rectangular polygon such as the following?
POLYGON((530934.26 161504.25,
530941.99 161515.92,
530945.36 161513.76,
...
2
votes
1answer
19 views
Scripted generating then loading rasters to PostGIS - raster2pgsql phantom
(Ubuntu server 18)
It seems that raster2pgsql is included in the postgis package but I can't find it separately to download either source or binary. Supposedly there's an executable somewhere in the ...
2
votes
1answer
43 views
Why PostGIS st_transform length is different?
I have two query
select sum(st_length(shape)) from sde.ways;
result is: 24268
And
select sum(st_length(st_transform(shape, 3857))) from sde.ways;
result is: 32047
Same Linestring geometries are ...
0
votes
1answer
51 views
GeoServer does not display/preview pgRouting Layer
I am developing a routing application using GeoServer, PostgreSQL, HTML and JavaScript. To display the routing layer on the HTML webpage, I followed the instructions from this workshop. GeoServer does ...
3
votes
1answer
90 views
Change datasource of multiple layers from one db to other db in QGIS project?
I have a QGIS project with around 100 layers referring to a postgres database.
Since we're migrating to a new server, the database will be transfered too, but will be given a new IP. The connection ...
