Questions tagged [postgis]
PostGIS is an extension for the PostgreSQL object-relational database that adds support for geographic objects.
7,547
questions
1
vote
1answer
42 views
Get list of equidistant points on polygon border PostGIS
I would like to get points from polygon border using PostGIS.
I've tried using ST_DumpPoints() function, like this:
CREATE OR REPLACE FUNCTION get_points_from_polygon(polygon geometry)
RETURNS SETOF ...
1
vote
1answer
55 views
Using ST_AsMVT with ST_TileEnvelope clipping in mapbox
I have been searching for a while for a way to correctly project the ST_AsMVT to ensure it fits to the map. The below query was the only suggestion I found that will correctly transform to the mapbox ...
1
vote
0answers
42 views
How to obtain a live route on a carto map? [duplicate]
My main goal is to build an app that send lat lon from my smartphone to my carto (postgis) database and show the route "live" (in real time) as a line and not as a lot of points.
i am able to save ...
2
votes
1answer
75 views
Adding feature to PostGIS layer that has joins is really slow
I have a large project that I've been setting up to work with QGIS 3.12 and I have now hit a problem with using "joins" to join several attribute tables with a geometric one.
I have reduced the ...
0
votes
0answers
33 views
Convert list of GML coordinates into WKT
I am working with a NetCDF file. One of the attributes of the file is its footprint given as just a list of GML coordinates e.g.
#ncds is a netCDF dataset
>>> ncds['METADATA/EOP_METADATA/om:...
1
vote
1answer
14 views
Is there a way to tell PostGIS ST_Reclass to use double precision types?
I have a raster of slopes with one band with decimal data type (32BF- 32 bit float) values from 0-90 degrees.
I use ST_Reclass to reclasify the values to 8 categories:
ST_Reclass ( raster, 1, '[0-1):...
0
votes
1answer
44 views
Get the maximum distance between two consecutive points in a linestring [closed]
I want to check if a track is valid by measuring the distance between each consecutive points of my track and discarding the tracks which have two consecutive points really far away. My tracks are ...
0
votes
1answer
40 views
How to use ST_Distance (Closest polygon to point)
Can I know how to interpret this SELECT statement for PostGIS? I am unsure where do I insert my own coordinates in this statement.
SELECT geom
FROM polys, points
WHERE points.gid=1 AND ST_DWithin(...
0
votes
1answer
37 views
QGIS 3.x issue with PG hstore colums
I have a PostgreSQL 9.6 database with tables created by imposm that include a hstore column. This hstore column looks fine in pgAdmin4.
In QGIS 3.6 or 3.12, it shows up in the attributes ...
0
votes
1answer
23 views
Get all rows that have geometries which intersect with a geometry in any row of a different table in an effective way
I have two PostGIS tables containing thousands of geographic polygons each (each row has one polygon and numerous other attributes). Both tables represent different data sets but cover the same area ...
1
vote
1answer
44 views
QGIS Polygon Shapefile to PostgreSQL (PostGIS)
I am working on getting the polygon data of different areas loaded into PostgreSQL so that I can do searches within those areas based on address points.
I have downloaded postcode data from: https://...
1
vote
1answer
60 views
How to limit the search for facilities to only 1 unit with PG Routing? … Closest Facility in PostGIS?
I'm working in PGROUTING - POSTGIS
I have my road network and two sets of points -firestation and the entire cityblocks from town (in diferent tables, 1 for origin, and 1 for destination).
The plan ...
1
vote
1answer
39 views
Make array_append in trigger with PostGIS intersection
I've 2 layers : linestring with column id_line and polygone, drawn by user.
I trying to get id_line on linestring who intersects polygon.
I've got it in qgis with :
aggregate(
layer:= 'aep_cana',
...
0
votes
0answers
23 views
Percent of data pixels in a raster clipped to a buffer in PostGIS?
I have a raster created by unioning a set of rasters that are clipped to a buffer. I'd like to determine whether this "unioned raster" has a sufficient number of data (vs. no-data) pixels. I'm wanting ...
1
vote
0answers
133 views
Geodataframe to PostGIS or shapefile
I have a GeoDatFrame df :
df.geometry.name
>>>'geometry'
df.loc[:, 'geometry']
>>>
Identifiant
0 MULTIPOLYGON (((2.827 42.691, 2.828 42.691, 2....
1 MULTIPOLYGON (((2....
0
votes
0answers
43 views
How to modify the columns to geometric points
How make points geometric if already exist columns with data like column names are gps_latitued and gps_longititude in mapping table
1
vote
1answer
26 views
Intersection/Addition of multiple tables in PostGIS
I want to intersect multiple table together. For each intersection geometry, I want to sum the value of all the layers intersecting at that place.
My current query does it by step, getting the ...
3
votes
1answer
60 views
CSV containing GeoJSON format geometries to DataFrame
I have a CSV file I open as a DataFrame.
Among the columns is one named geom 1 :
df.loc[:, 'geom 1']
>>>
0 {'type': 'Polygon', 'coordinates': [[[2.826589...
1 {'type': 'Polygon', ...
4
votes
1answer
45 views
Write *.sql file with FME
Is it possible to write sql files containing all the CREATE TABLE... and INSERT... statements etc. with FME instead of directly execute them against the database (here: PostGIS)?
Background is that I ...
4
votes
1answer
78 views
Inserting new features to PG\PostGIS table with generated column using QGIS?
I'm using PG 12.2 with PostGIS 3.0.0, and I've created a table with a generated column:
CREATE TABLE public.test1
(
id integer NOT NULL DEFAULT nextval('test1_id_seq1'::regclass),
geom ...
0
votes
0answers
48 views
Convert from GDA2020 to GDA94 using proj string in PostGIS
Following this answer, I was able to convert from GDA94 to GDA2020 using proj string (I'm using PostGIS).
Now is there a way to convert it back? I tried to invert the signs from @JasonD answer but ...
0
votes
0answers
33 views
ogr2ogr cannot import .osm.pbf file to PostGIS
I'm trying to import a multipolygon layer from "colorado-latest.osm.pbf" into PostGIS. GDAL 2.4.2 has been installed in a Ubuntu system. However, whenever I'm trying to import the osm.pbf file, I get ...
2
votes
1answer
61 views
How to estimate time spent within a location using GPS/timestamp data in PostGIS?
I am new to PostGIS, and I am trying to estimate time spent in a location based off of interpolating time between GPS points (I am using PostgreSQL 12.2 and PostGIS 3.0).
I have GPS points stored ...
0
votes
0answers
47 views
GDAL conflicts between GDAL and PostGIS versions in R
I'm trying to learn postgis and I'm using R as an interface to make queries under Linux. I'm using GEOS 3.8.0, GDAL 3.0.4 and PROJ 7.0.0. When everything is up-to-date my R ecosytem (sp, sf, rgdal) is ...
0
votes
0answers
29 views
Importing a TIFF to GeoServer
I am working to upload a TIFF file into GeoServer so that it can be viewed in layer preview. I exported the TIFF from DroneDeploy, it has an associated geographic location. I was not able to get the ...
1
vote
1answer
33 views
Publishing PostGIS Data in Portal for ArcGIS Enterprise
I have a PostGIS table with millions of points (x,y,z,time).
I have to develop a web app with the ArcGIS JavaScript API, to make some statistics like this application:
https://ycabon.github.io/...
3
votes
1answer
76 views
ST_GeneratePoints returns the wrong number of points
I'm using PostGIS to generate random points on some bikeway data sets in my region, and I've run into an issue. For a few of the sets, I find that when I run my query I often don't get the right ...
1
vote
1answer
29 views
Export Raster tiles in JPEG/PNG format using PostGIS
I imported my image with three bands (RGB) with 100*100 tiles using raster2pgsql into the DB. Now, I need to export those tiles separately. I used the following code but it produces a table
select ...
1
vote
1answer
26 views
Filtering out polygons that consists of less than 3 nodes
I have OSM data stored in PostGIS.
After running the following query:
select st_astext(st_forcepolygonccw(st_transform(geom,32633))), st_geometrytype(geom) from osm_versions.polygon_v1
limit 10
I ...
0
votes
0answers
11 views
Building PostgreSQL 12.2 and PostGIS 3.0 on MacOS Mojave with GCC - Could I use Clang LLVM compiler?
I need to deploy PostgreSQL 12.2 and PostGIS 3.0 to a Mac Pro server for Tiger and OSM tiles to provide a GIS Developer workstation.
I've done this before several times but never successfully built ...
0
votes
0answers
33 views
Import PostgreSQL into MS Access OBDC Error While Exectuing the Query #7
Trying to import a table from PostgreSQL to MS Access (2007) however, I keep getting the error
OBDC Call Failed - Error While Executing the Query #7
STEPS TAKEN
In OBDC Data Source Admin (32-bit)...
2
votes
2answers
45 views
ST_SnapToGrid makes result invalid depending on size
For fewer than 100 polygons in my set of ~1 million, I am getting a behaviour with ST_SnapToGrid that I do not understand. Depending on the parameter that I pass for size, the validity of the ...
0
votes
1answer
53 views
PostgreSQL 12 crash trying to add PostGIS [closed]
When I try to execute CREATE EXTENSION postgis;
I get this error :
geoall=#CREATE EXTENSION postgis;
server closed the connection unexpectedly
This probably means the server terminated ...
0
votes
0answers
44 views
Restore a PostGIS database cluster properly
Context
In order to migrate from PostgreSQL 10.12 to 12.2 on a Ubuntu 18.04.4 LTS server, I first uninstall and purge all postgres related stuff then I followed this;
https://www.postgresql.org/...
0
votes
1answer
34 views
How is the first vertex in a polygon chosen?
I was wondering about the method/specification of choosing the first vertex in a polygon. Let's say that I have polygon that has 4 vertices, ([0 0] [10 0] [10 10] [0 10]). Which of them will be the ...
0
votes
0answers
31 views
Converting POLYHEDRALSURFACE to (MULTI)LINESTRING or POLYGON?
99% of my table are MULTILINESTRINGs, but for some reason a few hundred are stored as POLYHEDRALSURFACE. How can I convert the following into MULTILINESTRING, LINESTRING, or POLYGON?
...
0
votes
0answers
39 views
Loading raster on PostgreSQL/PostGIS Linux server not working
I used the following raster2pgsql.exe to upload an SRTM mosaic to my Linux database server (working fine for vector data):
raster2pgsql.exe -I -t auto e:\data\Sinai\SRTM90V41b\SRTM41M90.tif SRTM90c | ...
0
votes
1answer
34 views
set z-value from table
In qgis 3, postgis, no DEM and zero python knowledge.
I've got linestring 3d but Z coordinates is empty, Z-values are stored in table.
Z1 for the first node, Z2 for the last node.
I found a lot of ...
0
votes
0answers
20 views
GeoServer + PostGIS: multiple tables or SLD filters
I'm setting up a PostGIS and GeoServer for OpenLayers use and started wondering how to achieve the best performance in selecting and rendering the geometries.
Is it better to create multiple tables / ...
1
vote
1answer
73 views
QGIS add PostGIS / Spatiallite rows as layers
I wish to be able to query PostGIS and Spatialite tables and add vectors from individual rows whose attributes meet criteria as multiple layers under a group or sub group instead of a single layer for ...
2
votes
1answer
75 views
psql changing comma to decimal for importing CSV file
I try to import a CSV file into a PostgreSQL database that runs on a remote server.
I use psql via SSH.
AIS_DB=# CREATE TABLE bet_srk_2008 (
AIS_DB(# Unique_VesselTraject numeric,
AIS_DB(# ...
0
votes
0answers
22 views
Embedding a Leaflet user click Lat, Long into PostGIS SQL? [duplicate]
I have ran the SQL through a web browser and can connect to the server which returns the result. I just can't get it to work in Leaflet. Any suggestions?
<script src="js/leaflet.js"></script&...
1
vote
1answer
33 views
RiverGIS under QGIS - Not able to execute “Extracting Manning's n values for cross-sections”
I am using RiverGIS plugin in QGIS and facing a problem in Extracting Manning's n values for cross-sections. The error code is as follow: -
Extracting Manning's n values for cross-sections
...
-2
votes
1answer
78 views
Good explanation for eps (distance) parameter in ST_ClusterDBSCAN
PostGIS documentation is horrible. I've read an entry for ST_ClusterDBSCAN multiple times, I've even tried in real world scenario. However I still got the feeling that I misunderstand eps parameter.
...
0
votes
1answer
94 views
PostGIS Vector Tiles zoom affects tile production
I'm building a small tile service to present postgis data on the fly to a leaflet map.
for testing I am using the data set ne_110m_admin_0_countries
The first problem is the extent of the tiles ...
2
votes
1answer
50 views
How to cluster Points with ST_ClusterDBSCAN taking into account their type stored in a separate column?
I was looking to use ST_ClusterDBSCAN on a set of points that also have a type. So I want the points to be clustered by type, so that points having different types won't end up in the same cluster. ...
0
votes
0answers
18 views
PostGIS GeoJSON not displaying in QGIS
I have loaded the following GeoJSON into PostGIS using Java and the table is there but the line will not display. If I zoom to feature it correctly shows the extent, it just doesn't draw it. I've ...
2
votes
3answers
54 views
PostgeSQL Trigger St_Intersect Insert, Update
I have a problem creating a trigger in PostgreSQL that will get data from polygon layer.
Issue:
I have two tables:
1. point (id, geom, address)
2. polygon (id, geom, address)
Polygon table ...
0
votes
1answer
31 views
Storing a point coordinate in PostGIS
I'm finding a hard time storing a point coordinate as the location to a PostgreSQL using PostGIS:
I found this query, but I'm unable to understand what the SRID and CRS values specified in this ...
3
votes
1answer
84 views
ST_AsMVT vector tiles distort at low zoom levels
I'm serving up a data from PostGIS by converting it into a tile and then bytes and then onto a leaflet map. It all works, with one exception, the latitudes stretch ever greater towards the poles when ...
