New answers tagged postgis
0
votes
Save xml CityModel files to PostGIS
CityGML Data such as Level-of-Detail 1 can be uploaded with the 3D City Database Importer / Exporter Tool downloadable here: 3D City Database.
The setup is relatively straightforward and documented ...
- 151
3
votes
Accepted
pgRouting computation time & code efficiency
Instead of calling pgr_dijkstra multiple times you should be invoking it with 2 arrays for sources and targets and not single values (thank you @DavidP for highlighting this). This way the graph will ...
- 842
1
vote
Creating a grid which takes coordinates as an input
So, if I understand the question correctly, there are many ways to solve your question, and this is one of them. Proceed as follows:
Replace the values of the northwest corner coordinate 100,100 with ...
- 3,912
2
votes
Showing Origins on ARRAY multiple routes - pgRouting
Let's say you want to compute a route from A to B then your start and destination vertex will be in the output as start_vid and end_vid. For the sake of simplicity this query is using some arbitrary ...
- 842
2
votes
Grouping polygons by similarity keeping records of ids
Depending on the actual similarity (vertex distribution in particular), you could probably get away with groups over gridded centroids, e.g.
SELECT
ARRAY_AGG(id) AS ids,
ST_Union(geom) AS geom
...
- 23.3k
2
votes
Accepted
Want to use PostgreSQL db and ST_AsMVT() to develop a tile server, assuming my start point is a GeoTIFF
So with the help of @geozelot i was able to conclude the following:
I cannot use vector tiles for my geoTIFF as it is raster data
To accomplish my initial goal generating a tile server for raster ...
- 63
0
votes
"libSFCGAL.so.1: undefined symbol" upgrading to Postgis 2.2.2 (and 2.2.1)
In case anybody faces below error
ERROR: could not load library "/usr/pgsql-12/lib/postgis-3.so": /lib64/libSFCGAL.so.1: undefined symbol: ...
0
votes
Buffer with PostGIS in the form of a pentagon or hexagon
Based on dr_jts response, I propose the following version of the design and name of the new spatial SQL function:
This simple spatial SQL function takes as input both one geospatial point and many ...
- 3,912
3
votes
Deleting small holes in polygons with PostGIS
Based on dr_jts response, I propose the following version of the design and name of the new spatial SQL function:
Create a spatial custom SQL function:
CREATE OR REPLACE FUNCTION ...
- 3,912
7
votes
Deleting small holes in polygons with PostGIS
Just to add a little less convoluted solution in SQL, shifting construction logic to ST_BuildArea - the LATERAL join predicate is an elegant method to expand and iterate over sets of rows returned ...
- 23.3k
0
votes
ERROR: function st_straightskeleton(geometry) does not exist
You need the extension postgis_sfcgal
ex)
CREATE EXTENSION postgis_sfcgal;
- 1
13
votes
Accepted
Deleting small holes in polygons with PostGIS
This is one of those processes which are simple in an iterative language, but trickier in set-oriented SQL. But the ST_DumpX set-returning functions in PostGIS and the array handling in Postgres make ...
- 3,621
3
votes
Deleting small holes in polygons with PostGIS
Based on your example, just extract the interior ring of your geom, calculate it's area and use it as condition in where clause as:
SELECT gid, ST_Collect(ST_MakePolygon(geom)) As geom
FROM (
...
- 423
0
votes
Split lines by polygons PostGIS
I had the same problem, and you're solution didn't fix it.
I first found a workaround splitting the multilines twice : it worked better, but the split were not always precisely at the intersection ...
- 506
5
votes
Accepted
st_dump - why is the "path" an array?
It is possible to have nested collections, and in this case the array makes sense:
select path, st_asText(geom)
from st_dump('GEOMETRYCOLLECTION(MULTILINESTRING((1 2,3 4),(3 4,4 5)),MULTIPOINT(1 2,3 ...
- 34.4k
1
vote
Accepted
Why is h3.polyfill(geojson, 15) returning empty list for this polygon?
You need to use h3.polyfill_geojson(geojson, 15) or h3.polyfill(geojson, 15, geo_json_conformant=True)
- 7,763
0
votes
Getting base Line of PostGIS skeleton
A simple approach to "trim the branches" could be by longitude but is not a complete solution.
with lines as (
SELECT
st_subdivide(
st_straightskeleton( ST_GeomFromText('POLYGON (( 190 190, ...
- 423
3
votes
Accepted
Get geohash neighbors with PostGIS
I had have to do this a while ago, but I had to recreate from the top of my head here; this should be correct for all but the top-level 1-digit hashes¹:
WITH
geo AS (
SELECT
'dqcjqc' ...
- 23.3k
6
votes
Accepted
How to make column in view to have correct srid of origin table
You need to properly cast the column type to include the respective type modifiers like so:
CREATE OR REPLACE VIEW myview AS
SELECT
a.id,
ST_SetSRID(dmp.geom, 25832)::GEOMETRY(POLYGON, 25832)...
- 23.3k
5
votes
Accepted
Adding a polygon to a multipolygon column
I strongly recommend using a trigger to work this out - apart from some other things they have less confusing side effects, and can be extended much easier:
CREATE FUNCTION ...
- 23.3k
0
votes
Adding a polygon to a multipolygon column
Something using st_multi should do it:
UPDATE mytable set geom = st_multi(polygon);
should do it for you.
- 74.7k
3
votes
How to get a list of lines, with a list of polygons that they intersect?
This is generally solved using an [INNER] JOIN, or if you are also interested in lines that do not intersect any grid cells in the result set, a LEFT JOIN; you then want to GROUP BY your line_ids and ...
- 23.3k
4
votes
Accepted
In PostGIS, how to identify intersecting line segments, but not matching start and end nodes
To find lines which intersect in the interior of both lines, a query is:
CREATE TABLE intersection_points as
SELECT
ST_Intersection(a.geom, b.geom) AS intpt
FROM a
JOIN b ON ST_Intersects(a....
- 3,621
1
vote
Can't import a shapefile with different SRID in a geometry column
st_setsrid will return a new geometry with the updated projection. It is not an in-place update
UPDATE myTable
SET geom = st_setSRID(geom,4326);
- 34.4k
2
votes
Is an SRID unique?
EPSG:4326 isn't unique even for EPSG codes.
Within the EPSG Geodetic parameter dataset, EPSG:4326 can either refer to a CRS or an Extent.
EPSG:4326 (extent)
EPSG:4326 (CRS)
For uniqueness you need ...
- 11.8k
1
vote
Use PostGIS to find all the most outer points
Thanks for some good comments I made it work using.
SELECT ST_ASGEOJSON((ST_DumpPoints(ST_ConvexHull(st_collect(geog::geometry)))).geom) FROM sources;
0
votes
Accepted
Insert a waypoint into a POSTGIS table with psycopg2 and ST_GeographyFromText
You need to enclose your Point into single quotes:
SELECT ST_GeographyFromText('POINT(48.07093 11.19797)')
->
vs
SELECT ST_GeographyFromText(POINT(48.07093 11.19797))
ERROR: syntax error at or ...
- 6,698
0
votes
Geometry Viewer in pgAdmin 4 displays a straight line instead of polygons of European union countries
You probably need to change -- by using ST_Transform() -- the coordinate reference system (CRS) of your geometries, e.g. by using such query instead of a standard SELECT:
SELECT
ST_Transform(geom, ...
- 6,698
1
vote
Accepted
Calculating the distance For each Row with respect to a Common Point
It looks like 'port.longitude' and 'fac.latitude' are correctly replaced in your f-string, but 'Warehouse.longitude' and 'Warehouse.latitude' are not and are left as string. The reason is you try to ...
- 1,747
-1
votes
Load Shapefiles into a remote PostGIS db using command line?
const postGISdb = "database";
const user = "postgres";
const password = "password";
const host = "localhost";
const port = 5432;
exec(
...
0
votes
Counting stacked polygons in PostGIS
Let's assume there is a polygon layer called 'poly_test', see image below.
Extending the idea from @geozelot it is possible to count the polygons via grouping by their geometry.
With the following ...
- 23k
1
vote
Buffer with PostGIS in the form of a pentagon or hexagon
Not sure what you're asking. The title says pentagon or hexagon but "How would you buffer a point to a indivisiable 4 sided polygon." sounds like a square:
select row_number() over() as id, ...
- 51.4k
0
votes
Saving a TIN given as a Wavefront OBJ file into PostGIS internal TIN format
As a DIY solution, after diving into the galaxy of 3D related stuff, I found a nice Python (>= 3.7) MIT licensed package called meshio (Schlömer, Nico. (2022). meshio: Tools for mesh files (v5.3.4)....
- 6,698
3
votes
Importing single-geometry data to postgis
This occurs due to the historic (and poor) decision to allow both multi and single geometries in the same Shapefile. Most database stores don't allow you to mix geometry types in a column so there is ...
- 74.7k
1
vote
Accepted
PostGIS function that updates WKT geometries to geometry type with st_geomfromtext
Before the update:
Run
SELECT
pa.attrelid::REGCLASS, pa.attname
FROM
pg_attribute AS pa
WHERE
pa.attrelid = '<schema>.<table>'::REGCLASS
AND
pa.attnum > 0
AND
pa.attname ...
- 23.3k
2
votes
PostGIS: get Circle to GeoJSON
As said in the comments, circle is a native postgres type that is not related to postgis. If using PostGIS, it is better avoid these native types.
That being said, if you really have to use the circle ...
- 34.4k
0
votes
QGIS asks for PostgreSQL credentials multiple times
In my case, removing the "sslmode=disable" text from each y the .qgs file solved the issue.
Looks like new layers added in my project do not add the sslmode param and old ones (maybe added ...
- 3,500
1
vote
Accepted
raster2pgsql no more INSERT/COPY statement in the log
On Linux (more info):
raster2plpgsql [<options>] <raster> >> /dev/null
On Windows (more info):
raster2plpgsql.exe [<options>] <raster> > NUL
- 1,237
2
votes
undocumented PostGIS ST_Distance function taking text as parameters
Very short, but you should be able to understand it:
postgres=# SELECT ST_Distance('POINT(0 0)','POINT(1 1)');
st_distance
--------------------
1.4142135623730951
(1 row)
or more explicitly:...
- 6,698
6
votes
Accepted
undocumented PostGIS ST_Distance function taking text as parameters
The function ST_Distance(text, text) is defined by PostGIS. Here's the source code:
-- Availability: 1.5.0 - this is just a hack to prevent unknown from causing ambiguous name because of geography
...
- 3,621
1
vote
Accepted
psycopg2 cursor.copy_expert() BadCopyFileFormat: extra data after last expected column when writing WKB data
The solution was to use .wkb_hex in order to have a plain string (not a binary one, which would have been OK though for a usage by ST_GeomFromWKB()):
import psycopg2
from io import StringIO
from ...
- 6,698
5
votes
Accepted
Rounding decimal numbers in PostGIS
Via Round ST_Distance you need to cast the values to a numeric:
select
name,
round(ST_X(geom)::numeric, 2) as x_coordinate,
round(ST_Y(geom)::numeric, 2) as y_coordinate
from cities
You could ...
- 8,163
2
votes
PostGIS performance using ST_Subdivide
Hello @marieneighe If I were in your shoes, my question is only;
To Subdivide or Not To Subdivide
As a matter of routine I refer to the below when a new dataset comes in
Do a test on the size of the ...
- 427
0
votes
Should I use different projections to calculate distance and displaying on the Map?
From what I can gather of your question, you are mixing the display of the map with the output of the calculations, which will be a table.
...the output of the query...
is tabular??
Use the ...
- 427
0
votes
Speeding up a TIN to MultiPolygonZ conversion in PostGIS
Atm was actually right in his comment. And indeed, it's actually a nonsense to tessellate a TIN!
It was an error on my side.
Here is a detailed comparison for a same query with and without the use of ...
- 6,698
Top 50 recent answers are included