Questions tagged [postgresql]
PostgreSQL is an open source object-relational database system.
3,909
questions
2
votes
2
answers
21
views
PostGIS Maximum Inscribed Circle
I'm attempting to utilize the PostGIS function ST_MaximumInscribedCircle() but having some trouble interpreting the example. How can I feed the geometry into a from clause to retrieve the center and ...
- 1,019
3
votes
1
answer
48
views
PostGIS Nearest neighbor really slow
I'm trying to set a link between two tables (db1 and db2) with a nearest neighbor approach and some condition checking.
SELECT id, label_shop, linked_rows.*
FROM shopitem.shopitem_database shop
LEFT ...
- 61
1
vote
0
answers
10
views
How to derive raster values fast into the gird?
I have a grid that has the same dimension as a raster (Sentinel 2 imagery) which is 10 x 10 km. In addition inside the grid, I have 1000 rows and 1000 columns which make each cell 10 m resolution. ...
- 99
0
votes
1
answer
8
views
Osm2pgsql data dump in specific schema not working
While using osm2pgsql 1.6 i am trying to dump PBF into specific schema of postgres DB using following commands using docker. (i am setting database properties in docker env)
docker run -v /mnt/ne-...
- 1
1
vote
1
answer
18
views
Wrong schema while importing data via with ogr2ogr into postgresql database and using dblink
I have the following issue:
I have a table on a PostgreSQL database which triggers its changes via dblink to another table in a different data base. The trigger function looks as follows:
CREATE OR ...
- 331
0
votes
0
answers
20
views
How to correctly/efficiently store Sentintel-2 imagery with 100 x 100 km dimension?
I am trying to store 100x100 km Sentinel-2 tile imagery into the PostgreSQL table.
I have only these bands: B2, B3, B4, B8, and B11, resulting in a 2.5 GB GeoTIFF file. Consequently, due to the large ...
- 99
1
vote
1
answer
26
views
Reducing shapefile size from PostgreSQL using ogr2ogr
I have a PostGIS database created from an old AutoCAD system. Because of this, every column is type varchar(254), even though actual data lengths are small. I need to export tables as shapefiles, and ...
- 11
-1
votes
0
answers
12
views
Linestring that crosses a polygon the points where the line length intersects with the polygon in km + m
I have to identify a linestring that passes through a polygon and when the linestring intersects the polygon boundary at the input and output I have to give km + m input and km + m output from the ...
- 515
0
votes
0
answers
24
views
PostGIS error execute query [closed]
I'm trying to run a Query that does a larger operation in the PostGis database:
The code is:
$q = "SELECT
ply.ogc_fid AS pid,
lns.ogc_fid AS lid,
ST_LineSubstring(...
- 515
2
votes
1
answer
25
views
How can I get the closest linestring in my database to the linestring drawn by some user, following a path?
I have to take a geometry drawn by an user and return the geometry in my database that represents the drawing. I tried in PostGIS
In my database I have a column 'way' with the geometry that I want to ...
3
votes
1
answer
26
views
Pass a variable parameter to pgr_dijkstra (pgRouting) Edges SQL
I need to pass a parameter to Edges SQL in a pgRouting function, for example pgr_dijkstra.
I try something like:
PREPARE test (int) AS
INSERT INTO elab.test_f
SELECT * FROM pgr_dijkstra(
'SELECT id,...
- 503
2
votes
1
answer
52
views
PostGIS st_intersection returns only the lines that are inside the polygon
I have two polygons called 1, 2.
I have a linestring that crosses the two polygons but does not remain only inside them.
What I'm trying to do:
Find the linestring that is from A to D in the case of ...
- 515
3
votes
1
answer
32
views
GRASS GIS: Execute SQL query and save output
I'm trying to find a way in GRASS GIS to execute a complex SQL query (with JOINs) and save it.
SELECT a.id, a.geom
FROM schema01.table01 a
JOIN schema01.table02 b
ON a.geom && b.geom AND ...
- 76
3
votes
2
answers
34
views
Convert linestringz to linestringm using alter column?
I am attempting to import linestring data with or without m values, then test whether the data has m values, then add m values to the geometry if they are missing. So far, I have working tests for ...
- 6,400
5
votes
2
answers
70
views
Calculating cost values in pgRouting using ST_DWithin
I have a table where there are two geometries: geom_way (for routing roads) and (osm_meta) which is a triangulation from polygons. I use ST_DWithin function (between both geometries) with a distance ...
- 91
2
votes
1
answer
83
views
Merge multiple tables (100 tables) into a new table in PostGIS
I am looking to merge a large number of individual tables into a new table in PostGIS. I have almost 100 "identical" tables so I wanted to do it automatically. There is a good answer here ...
0
votes
0
answers
39
views
PgRouting withPoints from point to point
How create points_table for pgr_withPoint function? I know that table must have: pid, edge, fraction, side. For example I have point A(lat,lon) and point B(lat,lon), and I want to get route from A to ...
- 1
0
votes
0
answers
18
views
Connecting to RDS PostgreSQL with QGIS with SSH?
I’m still kind of learning and this is new to me so forgive me if I’m using the wrong terminology or wrong tools.
I’m trying to connect a local client (QGIS) to a PostgreSQL database hosted on an RDS ...
- 35
3
votes
3
answers
99
views
Remove only certain squares from grid in PostGIS
So I have this grid (I create this from oriented enveloping the brown polygons and then buffering)
create temp table test_squares as select test_fishnet.poly from test_fishnet, polys where ...
- 141
1
vote
1
answer
15
views
ogr2ogr list of layers created
Using a command such as the following:-
ogr2ogr -f PostgreSQL "postgresql:..." \
-lco SCHEMA=my_schema \
foo.xlsx
on a Excel sheet with multiple tabs will create multiple tables in schema ...
- 700
0
votes
0
answers
44
views
Unable to connect to a PostgreSQL database
I've been accessing a PostGIS database in QGIS I created on an old laptop and now trying to reconnect to it with a new laptop. I've installed PostgreSQL 14 whilst the database I want to connect to was ...
- 2,089
0
votes
0
answers
23
views
How to get all the points ina bound from PostGIS
I have a table that consists a column names geos and is of Linestring field but I need to fetch the data using bounds.
But not sure how to do it.
I tried below query but nothing is coming.
SELECT NULL ...
- 101
1
vote
0
answers
25
views
Create db connection from ArcMap or ArcGIS Pro to a regular non-spatial PostgreSQL database
When I attempt to create a database connection via ArcMap or ArcGIS Pro to a pure tabular PostgreSQL database, I keep getting insufficient permissions error.
I know it is not a permissions issue.
Is ...
- 11
5
votes
2
answers
59
views
Removing certains routes from pgrouting output table
I have used pgRouting to generated multiple many to many traffic routes different points of interest. I have used st_Dwithin to put a catchment area on the routing alogirthm, however the agg_cost(...
- 141
2
votes
0
answers
17
views
Retrieve properties for a feature in MVT from JSONB column?
TL;DR Check the last UPDATE section.
We store all of our geometries in a single hybrid PostGIS table and corresponding properties in a single JSONB column. Properties can be anything and differ for ...
- 307
2
votes
1
answer
32
views
How to convert raster file into a series of SQL commands
I am trying to save raster data to the PostgreSQL database. I know that there exists raster2psql.exe, but this command limits me from adding additional columns to the table. For this reason, I would ...
- 99
4
votes
1
answer
109
views
Convert bytea to geometry column with PostGIS
I am trying to create a geometry column from bytea.
I imported OSM data using ogr2ogr using the command
ogr2ogr -f PostgreSQL -nln bahamas_multipolygon PG:"dbname=openstreetmap user=postgres ...
- 822
0
votes
0
answers
32
views
Update OSM database failing
I'm trying to udapte a OSM database that I just imported. After following the steps https://ircama.github.io/osm-carto-tutorials/updating-data
I don't manage to pass the first udpate. I always get the ...
1
vote
1
answer
29
views
Save xml CityModel files to PostGIS
I'm trying to save CityModel xml files to our PostGIS Database with the files looking like this:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<core:...
- 151
2
votes
1
answer
107
views
pgRouting computation time & code efficiency
I am trying to find the closest landfill by distance from various locations, however, the computation time is 8 hr 36 min to find the shortest path from 3602 locations to 5 landfill locations.
Before ...
- 203
2
votes
1
answer
33
views
Showing Origins on ARRAY multiple routes - pgRouting
I have routed between multiple points using pgRouting. I have aggregated the total cost of each route (length) into a new table using edge = -1 to be imported into an inverse distance weighting ...
- 141
3
votes
1
answer
56
views
Grouping polygons by similarity keeping records of ids
I have a layer made with overlapping polygons, some areas has the same feature overlap 100 times. What I want is to aggregate all the ids for those features which I have determined they have ...
- 226
1
vote
0
answers
32
views
GeoServer JDBC connect error
I installed a clean version of the GeoServer 2.20.4 on Windows, I create "User Group Services" JDBC to connect to the PostgreSQL database and add a user, create "Authentication ...
- 11
2
votes
1
answer
58
views
Creating a grid which takes coordinates as an input
I need to create a grid, based on parameters.
Dimensions of the grid (10 km x 10 km)
Upper-left coordinate, which defines the start of the grid (upper-left corner)
I was reviewing the following ...
1
vote
0
answers
39
views
Load shapefile from local machine to database PostgreSQL
I have a method that imports shapefile to PostgreSQL (using GeoTools), it takes as arguments the path of the shapefile (it's working fine). The problem is I want the user to select a shapefile from ...
- 61
3
votes
1
answer
85
views
Want to use PostgreSQL db and ST_AsMVT() to develop a tile server, assuming my start point is a GeoTIFF [closed]
So basically I want to use ST_AsMVT() function to generate a web server that generates vectors tiles for me, which can then can be accessed through a link like "http://localhost:8080/{z}/{x}/{y}....
- 63
3
votes
1
answer
71
views
How to make column in view to have correct srid of origin table
I have the following view
CREATE OR REPLACE VIEW myview
AS SELECT a.id,
st_setsrid((st_dump(a.geom)).geom, 25832) AS geom
FROM mytable a;
on a table mytable with a geom column of the following ...
- 1,121
3
votes
1
answer
58
views
Get geohash neighbors with PostGIS
I’d like to get the 8 neighbors of the geohash grid. For example, the neighbors of the hashcode dqcjqc are dqcjqf,dqcjqb,dqcjr1,dqcjq9,dqcjqd,dqcjr4,dqcjr0,dqcjq8.
I wonder how to do it in PostGIS?
...
- 267
5
votes
2
answers
64
views
Adding a polygon to a multipolygon column
I have a table that contains a multipolygon column.
In my application, I use a view on the table which splits the multipolygon in several polygons:
CREATE VIEW myview
AS SELECT a.id,
ST_SetSRID((...
- 1,121
3
votes
1
answer
55
views
Area calculation for non-planar surfaces
I am looking for a way to calculate the area for a big set of 3D-surfaces.
I know, that some of those surfaces are slightly non-planar, which means that the coordinates got somehow "rounded" ...
- 31
0
votes
0
answers
57
views
ora2pg SDO_Geometry to st_geometry
I am trying to migrate my database (Oracle) to postgres using ora2pg (v.23.1).
Unfortunately the script converts our SDO_Geometry datatype into
shape geometry(GEOMETRY,32632)
instead of
shape ...
- 2,534
2
votes
0
answers
21
views
Interpolated for every point in the raster space
I have here made a demo of my attempt turn points on a map into a with values raster then use St_interpolateraster and than send it back as geojson. It returns values and they are interpolated well. ...
- 51
2
votes
0
answers
36
views
POINT and VALUE to Multipoint
I have a table of geom(POINT) and a value. How can I turn turning this into a Multipoint geometry so I can use it in interpolate?
The end goal is to use this just using my Points and values.
SELECT ...
- 51
1
vote
1
answer
36
views
Use PostGIS to find all the most outer points
I am trying to use POSTGIS to find all the outer points when I have alot of points? How would I go about doing this and and I cant seem to find a command for it.
1
vote
1
answer
56
views
Geometry Viewer in pgAdmin 4 displays a straight line instead of polygons of European union countries
Via PostGIS I import a shapefile layer with polygons of EU countries. I try select all rows and after still display blue straight line without the end. Where is the problem?
- 11
1
vote
0
answers
28
views
geog GEOGRAPHY(Point) insert creates syntax error [closed]
Why does this return syntax error at or near "5.1963". The documentation clearly states this is how to do it from my understanding?
http://postgis.net/workshops/postgis-intro/geography.html#...
- 51
2
votes
1
answer
62
views
PostGIS function that updates WKT geometries to geometry type with st_geomfromtext [closed]
I come from the Python world but I need to create a PostgreSQLl function that providing a table will:
Find columns that have "_wkt", i.e. "geom_wkt" or "geom2_wkt".
Do ...
- 23
0
votes
0
answers
32
views
Add/paste features to PostgreSQL table
I have a PostgreSQL database with several tables. One of them is a non-geometry table that is linked to other geometric tables. I am now trying to copy/paste data from a .csv file to this non-geometry ...
- 1
1
vote
1
answer
55
views
Calculating the distance For each Row with respect to a Common Point
I have two models with no relation Port and a WareHouse each with a lat and long fields. I want to be able to get the distance for each warehouse from the Port and maybe get the closest e.t.c.
This is ...
- 113
0
votes
0
answers
34
views
Export 3D data from PostGIS to any common open and standardized 3D file format
I am mainly curious, because I found this function: ST_AsX3D() in the list of PostGIS 3.2 "ST_As{something}" functions:
st_asbinary, st_asbinary, st_asbinary, st_asbinary,
...
- 6,738