Questions tagged [postgis]
PostGIS is an extension for the PostgreSQL object-relational database that adds support for geographic objects.
7,257
questions
0
votes
0answers
39 views
{'code': 'NoMatch'} OSRM API: What does it mean? And how can I avoid it?
I have RAW GPS trackers data, when I match those GPS coordinates to nearest NodeID, it gives me the No Match Error. I am using the following code snippet:
import requests
url = 'http://router....
2
votes
1answer
34 views
How to combine GEOMs or linestrings from different rows
I am working with a set of data that includes common IDs and overlapping linestrings/geoms. I would like to combine the overlapping lines based on their common IDs, preferably in postgres. I can run ...
2
votes
1answer
68 views
ST_Contains and ST_Intersection in PostGIS can not get the right result
I have two polygons n1 and n2, both are valid geometries.
I want to test the functions st_contains and st_intersection in PostGIS. I imported n1 and n2 into postgis, then I execute the following SQL:
...
1
vote
1answer
64 views
Matching GPS trackers POINT data between Nodes of OSM (Open Street Map) [closed]
I have GPS point data of vehicles from XYZ Tracking Company. The data has following columns:
UnitID
RecordDateTime
LATITUDE
LONGITUDE
Speed
Direction
Reason
Altitude
Data Dictionary is given below:...
1
vote
1answer
57 views
QGIS and ArcGIS Pro give different results on spatial join
I have two point layers that one has 2885 and the other has same 2885 plus 1748 more features. The second one is a result of "v.net.centrality" analysis and now I want to join the result spatially ...
2
votes
1answer
36 views
Combining grid of points to closest distance of several subqueries results using PostGIS
Say I have a table "table1" with a bunch of points (grid), and another table, "table2", with a bunch of lines (say they are roads), with a column "col1" (say it's a type of roads). So:
table1:
id;...
1
vote
0answers
42 views
How to insert a GeoJSON with different polygons into a PostGIS table?
I'm new with PostGIS and I don't know why when I insert into database a multipolygon, the value is empty.
I'm using PostgreSQL with PostGIS extension.
I create my table with this code:
CREATE ...
1
vote
2answers
74 views
function pgr_pointsaspolygon(unknown) does not exist
Im trying to run the query below in postgresql/postgis:
SELECT ST_AsText(pgr_pointsAsPolygon('SELECT id::int4, ST_X(geo)::float8 AS x, ST_Y(geo)::float8 as y FROM junction WHERE id IN ( 1050062040,.. ...
4
votes
0answers
50 views
Implementing PostGIS query using sf in R to make ST_Intersection faster
I'm trying to implement this query that speeds up st_intersections by using faster functions, namely ST_Contains and ST_Within, to do ST_Intersection only where absolutely necessary, from PostGIS to R
...
0
votes
1answer
40 views
Creating geodatabase using QGIS [closed]
I am using QGIS and am looking to get started on creating a geodatabase. I have point, line and polygon data. I want to arrange my database in categories that can store various point, line and polygon....
1
vote
1answer
47 views
GeoServer Connection Pool uses most available connections and not releasing them
We are using GeoServer with a geoserver cache for some layers. Most of our layer updates frequently and therefore we do not want to setup a cache for them (they do have in memory cache though).
...
2
votes
1answer
32 views
PostGIS Recursively Finding Intersections of Polygons to Determine Clusters
My dataset currently contains about 15.000 buildings which partially touch each other.
The objective is that those which do touch should belong to a cluster.
This "basically" works but the main issue ...
1
vote
1answer
56 views
Insert QGIS processed raster image (e.g. GeoTIFF) to PostgreSQL/PostGIS database by pyqgis
I've already connected to my database by a script, and loaded a vector a layer to QGIS.
Is there a way to upload a raster layer back to the database?
tablename = "***"
geometrycol = "***"
from qgis....
2
votes
1answer
52 views
Make 3D polygon from Point Z in PostGIS
I am trying to create a Convex Hull or ConcaveHull as a polygon Z from a set of points Z which I am grouping by id.
I tried to use ST_ConvexHull but it's returning Polygon in 2D, not in 3D.
My ...
2
votes
0answers
58 views
Disable DELETE for PostGIS Role/USER yet allowing them To INSERT new Features?
I intent to block my USERS from deleting features on PostGIS Database. I disabled DELETE in pgAdmin as below but the USERS are now unable to save feature on the database using QGIS3 as client. On ...
1
vote
0answers
23 views
Progressive union of a large number of multiband rasters in PostGIS
I am deriving multiband rasters from airborne lidar imagery, and loading these into PostGIS along with associated metadata. Raster cell values are counts of certain point classes over a series of ...
1
vote
0answers
24 views
PostGIS - suppress errors
Is there any way in SQL to supress errors in PostGIS functions, for instance if some of your geometries are invalid but you want to report a null value for the output or something?
To give an example,...
2
votes
0answers
31 views
Create sequential ID column type when loading GeoDataFrame to PostGIS
Is there a way to define GID column as sequential (auto-incremental) when loading GeoDataFrame (created from Shapefile) to PostGIS?
I use Geopandas and "to_sql" method:
geodataframe.to_sql(tablename, ...
1
vote
1answer
40 views
Loading a shapefile into PostgreSQL using shp2pgsql and outputting geographical coordinates using Django to draw a polygon in Leaflet [closed]
I have been trying to put a shapefile into a PostgreSQL database. I then want to query the database with Django and output coordinates that I can use to draw a polygon in Leaflet.
So far I have the ...
4
votes
2answers
111 views
PostGIS doesn't automatically convert WKB to GeoJSON
I discovered that somewhere in the upgrades from PostgreSQL 9.5 and PostGIS 2.2 to PostgreSQL 12.1 and PostGIS 3.0 the behavior for transforming a row into JSON changed.
I am using the following ...
1
vote
2answers
59 views
PostGIS ST_Buffer with attribute preserve
I'm playing with PostGIS a little bit but I'm stuck on, I think simple thing - how to create buffer from points with attribute inheritence?
Simple st_buffer code:
CREATE TABLE buffer AS
SELECT ...
1
vote
0answers
26 views
Remove strange artefacts
I have a shapefile with only one polygon. There is something that I'd like to remove, some "almost line polygons" that are there, as you can see in the picture below:
How can I do that? I can move it ...
1
vote
1answer
38 views
Dynamic triggers with table_name in PostGIS
I want make a dynamic trigger with PostgreSQL/PostGIS like this. I try that triggers make more generic for use with many tables with the same pattern. When i use direct table's name in code, that's ...
2
votes
0answers
43 views
PostGIS - create polygons from clusters of a fix number of points
I have a set of about 120,000 points, not regularly spread.
I would like to cluster them in order to create groups of about 3,000 points, regardless the distance. The final goal is to create ...
1
vote
2answers
37 views
Loading GeoDataFrame to PostGIS when GEOM column contains MULTILINESTRING and LINESTRING
How can I load GeoDataFrame (populated from shapefile) to PostGIS when GEOM column contains MULTILINESTRING and LINESTRING e.g.:
LINESTRING (14.2384666 46.6304714, 14.2386703 46.6304123)
...
0
votes
1answer
58 views
Displaying data from standalone PostGIS using ArcGIS API for JavaScript? [closed]
I'd like to serve out data from a PostGIS server and consume it as a layer in the ArcGIS JavaScript API. Searching for "arcgis javascript postgis" I see information on ArcGIS Desktop, ArcGIS Server ...
3
votes
3answers
90 views
Intersecting Polygons to Build Boundary Hierearchy using PostGIS
I have a set of polygons that are stored flat in a table (these are OSM admin boundaries extracted through Osmium and osm2pgsql). Each polygon has one record for it. However, these polygons form ...
1
vote
1answer
39 views
Processing dbscan by group
I am new to Postgis and I need to do DBSCAN by commune.
I have the base dir_v2:
id_dir
geom
commune
I need to process clustering at least 1000 points in a radius of 1000 meters per commune but it ...
0
votes
1answer
113 views
Publish WFS from PostGIS SQL view [closed]
I am trying to create a public WFS service with GeoServer from a PostGIS SQL view, but this does not load in QGIS. If I publish from a PostGIS table the WFS service is loaded normally.
Is it possible ...
2
votes
1answer
61 views
Determine correct tolerance/epsilon for line simplification in different zoom-levels when generating vector tiles
I am writing my own vector-tile generator for a map renderer that I have written, but I am unsure of how to pick the correct epsilon value to use in the PostGIS method ST_Simplify depending on the ...
2
votes
0answers
37 views
PostGIS intersected polygon fails on contains
I have two functions, first I check if a polygon is within a defined polygon. If it's not, but on the border, I cut it with ST_Intersection, and get a new polygon. If I check the result, I get that it ...
2
votes
1answer
59 views
PostGIS - Find the “median” distance between two group of geometries depending on polygon area
I want to evaluate land distribution and concentration regarding to road network. Specificly, I'm trying to calculate the median distance between a shape of roads (multilinestring) and a shape of ...
0
votes
1answer
27 views
Making union of some raster coverages without the area between them
I have a set of raster coverages. Some of them are really far from each other. When I use ST_Union function to union them, the function fill the area between them.
I explain it in a simple example:
I ...
2
votes
0answers
50 views
Select GeoServer vector tiles feature using OpenLayers and show features attribute in popup?
I have installed vector tiles extension in GeoServer and published a PostGIS tables using this format. The requested vector tiles from GeoServer using Openlayers works fine. Now, I want to select and ...
11
votes
2answers
253 views
Select polygons with 5 or more points
I have two layers (polygon and point) in QGIS added from PostgreSQL database and want to select polygons from index grid with 5 or more points on them.
Is this possible to do in QGIS or in PgAdmin?
...
2
votes
1answer
44 views
Can a PostGIS table contain features with both single-point and multi-point geometries?
I have a geopackage containing point features. Most features are single points but some are multipoints. When I attempt to import to PostGIS using ogr2ogr, I received the following error:
ERROR: ...
2
votes
0answers
48 views
PostGIS how to use ST_Retile
How do you properly use ST_Retile https://postgis.net/docs/RT_Retile.html
there are no directions for it
tried:
update slopes.dem_slope set rast=st_retile(rast,100,100)
and
select st_retile('...
1
vote
1answer
29 views
Osmosis failed: ERROR: relation “pend_ways” does not exist
I tried to generate osm update using osmosis and osmium(omsium only for setting configuration.txt from existing russia-latest.osm.pbf).
I use osmosis like that:
osm2pgsql -append --slim --cache ...
1
vote
0answers
80 views
How to correctly append data from ArcInfo GENERATE file to PostGIS table using ogr2ogr
Using CentOS 8.0 with PostgreSQL 12, PostGIS 3, and GDAL/OGR 3.0.2. I am trying to append data in an ArcInfo GENERATE file to an exisiting table.
My ArcInfo GENERATE file input.gen looks like this:
...
1
vote
1answer
53 views
PostGIS - How to extract polygons in a rectangle by their centroid?
I'm new to PostGIS and I'm amazed what can be done with it!
Currently I'm running a query from a large database where I need to split the extracts into smaller chunks so that I'm not bouncing at the ~...
2
votes
2answers
54 views
st_within limit 1 chosing wrong polygon
I have a query where I'm choosing one polygon (parcel) that intersects a point. The problem I'm having is that when I run into a parcel that has multi-geometry, it often chooses the wrong polygon ...
2
votes
1answer
57 views
Round ST_Distance
I am looking for a possibillity to round the result of ST_Distance on two digits.
If I try
select g.id ,round(ST_Distance(g.geom, p.geom), 2) dist_m
I get:
Funktion round(double precision, ...
8
votes
2answers
460 views
ogr2ogr from file to PostgreSQL 12 table fails
Using CentOS 8.0 with PostgreSQL 12, PostGIS 3, and GDAL/OGR 3.0.1.
I want to append a file to a database table.
Previously (before upgrading to the new system) I used
ogr2ogr -f "PostgreSQL" PG:"...
1
vote
0answers
47 views
QGIS DB Manager won't connect to PostGIS database, but connection “successful” in browser
I have created a spatial database in PGAdmin 4 using PostgreSQL 11. When I connect to the database in QGIS, the connection is successful in browser, but when I go to the DB Manager, it says that I am ...
0
votes
0answers
19 views
Postgis 2.5. : GRANT SELECT ON spatial_ref_sys etc. not needed?
My last Postgis version was 2.0.*.
Now I install 2.5.3 in my mac.
On 2.0.* you need on installation do:
createuser -h localhost -U postgres -S -D -R apache
echo "grant all on geometry_columns to ...
2
votes
1answer
57 views
Optimizing PostGIS query on table using buffered point [duplicate]
I have a global dataset of ecoregion boundaires. There are ~809 unique polygons in this dataset. I've imported it into PostGIS with SRS of 4326 and built a spatial index using the function below
...
0
votes
0answers
106 views
Adding local map as background to dbeaver spatial viewer in offline computer
I'm using DBeaver (which is a universal database manager) to connect to a Postgis database on a offline computer (not connected to internet).
The spatial viewer of DBeaver uses Leaflet to display ...
1
vote
0answers
62 views
Error when trying to export PostGIS table to FGDB in QGIS
I'm trying to export a layer of about 180,000ish points into a file geodatabase. I have tried it numerous times and continually get this error.
Sometimes, it pushes 30,000 points into the feature ...
1
vote
1answer
62 views
Qgis 3 : QgsEditorWidgetSetup for date
I need to make a date format in QGIS 3 but, in the program I need to use,
QgsEditorWidgetSetup('Date',{'yyyymmdd'}), I want to get this:
But I don't know how to use QgsEditorWidgetSetup to make the ...
0
votes
0answers
11 views
Calculate centroid of points (group point) [duplicate]
I am new to PostGIS and I need to calculate the centroid of a group of points that have a cluster identifier (id_cluster). I need to get the latitude and longitude of the cluster centroid.
