New answers tagged postgis
0
This worked for me and doesn't require the ST_MakeLine step:
CREATE TABLE line_segments as (
SELECT ST_CollectionExtract(ST_Split(geom4326,
(ST_Dumppoints(geom4326)).geom),2) as geom4326
FROM mylinetable)
geom4326 is the name of the geometry column with the original line(s) in it.
My solution does the following:
1. Dumps the points (vertices) of ...
0
For someone trying to solve a similar issue, an official solution to the directionality problem (pointed to by Daniel Kastl) exists. Exercise 8 in Geometry Directionality from the PostGIS tutorial shows how to do it.
Code below in case the link does not work:
WITH
dijkstra AS (
SELECT * FROM pgr_dijkstra(
'SELECT gid AS id, * FROM vehicle_net'...
0
There is an official solution to the directionality problem (pointed to by Daniel Kastl). Exercise 8 in Geometry Directionality from the PostGIS tutorial shows how to do it.
Code below in case the link does not work:
WITH
dijkstra AS (
SELECT * FROM pgr_dijkstra(
'SELECT gid AS id, * FROM vehicle_net',
(SELECT id FROM ...
0
So, after some digging I now found out that this seems to be a bug by ESRI. Since our experiences with issues like that, there ESRI stated a "won't fix" even if they approved the issue as bug, I won't make the effort to report it. Expectation is again a "won't fix"... it's not worth the time.
For all others running into this issue I will describe it here:
...
0
Assume you have tables table1 and table2 filled with the random points generated inside POLYGON((0 0, -1 0, -1 -1, 0 -1, 0 0)) polygon (I use points here as it's easier to create them, it works the same for polygons though).
DROP TABLE IF EXISTS table1;
DROP TABLE IF EXISTS table2;
CREATE TABLE table1 (id bigint generated always as identity, geom geometry(...
1
Using example from here...
Your input example:
const place = {
lat: -122.2652671,
lng: 47.30995661
};
Create a point converter, using Custom Type Formatting:
const asPoint = p => ({
toPostgres: () => pgp.as.format('ST_MakePoint(${lat}, ${lng})', p),
rawType: true
});
Executing the query:
await db.oneOrNone(`SELECT * FROM table ...
0
Nearly perfect answer from @ThingumaBob
Only thing to extend ist that in his script the geometry type will only by geometry instead of Multipolygon. So i extended your example with the ST_Multi() function:
CREATE TABLE export.osm_adminareas_a_clip5 AS
SELECT a.fclass,
ST_Multi(ST_Intersection(a.geom, ST_MakeEnvelope(12, 47, 13, 48, 4326)))::...
1
@Vince gave an educational comment, @Cyril named it directly, and I like to avoid further confusion; the simple answer is:
clip those grometries!
All queries present in this post do one thing, one way or another, that is selecting every row whose geometries minimum bounding rectangle, the bbox, intersects the created envelope, another bbox.
And that's ...
1
Problem was found. No problem with CSS and GeoServer. Problem with datasource - PostgreSQL DB. Part of data was droped. So GeoServer can't render data which doesn't exist.
0
Try the following with both the overlap condition and the intersects
CREATE TABLE export.osm_adminareas_a AS
SELECT *
FROM public.osm_adminareas_a
WHERE geom && ST_MakeEnvelope(12, 47, 13, 48, 4326)
AND ST_INTERSECTS(geom, ST_MakeEnvelope(12,47,13,48, 4326))
The reason why I include the overlap condition is that the ST_INTERSECTS ...
1
I don't know a simple way to do this, you will need extra step. The first step would be to brake your line into parts that are in the same direction (left or right angles). Then for each of your parts you can assesed its "complexity" for exemple by comparing its length to the length of the line first_point - last_point (I've seen a paper that called that "...
1
With the elements we have here,
I'll show a way to transform MultiPoints and Point geometrie and add the Z information in your geometry.
create table public.multi_test as (
SELECT ST_GeomFromText('MULTIPOINT((-71.160281 42.258729),(-71.160837 42.259113),(-71.161144 42.25932))') as geom,
1515 as elevation)
I will simulate your dataset as follow, geometry ...
2
I finally found out why all this. It was because of an old style that was saved in the database and that referred to an older Version of the layer, where some attributes where missing before. In the new Version of the layer all new attributes where added at the end. Overwriting the style using QGIS 2.14 solved the problem.
0
I had a similar problem, trying to get the standard deviation of all of the values across a selected set of raster layers. Most existing functions seem to be designed for a single layer. I couldn't use @John Powell's solution because ST_Union() doesn't have a standard deviation function. My solution uses ST_DumpValues() and it should work for the mean (or ...
0
I never figured out why the strange behavior described in the question occurred, but I did fix the problem. I was able to get the expected geometries returned by simply converting the multilines to single geometries.
After that, this code block successfully split the lines at the polygon boundaries:
CREATE TABLE split.split As
WITH split As (
SELECT (...
0
Juste upgrade your PostgreSQL and your PostGIS to the last version
I got the same problem with postgreSQL 11.2 and postgis 2.5.2. Apparently this problem has been solved since then. I now run with postgreSQL 11.5 and postgis 2.5.3 and everything is fine
1
The example in the PostGIS ST_LineSubstring doc is a bit dated. Modern Postgres has the JOIN LATERAL feature, which allows for more compact and efficient SQL. Here's a simple example:
WITH
data AS (
SELECT * FROM (VALUES
( 'A', 'LINESTRING( 0 0, 200 0)'::geometry ),
( 'B', 'LINESTRING( 0 100, 350 100)'::geometry ),
( 'C', '...
1
Checking original geometry when using ST_Split function with MultiPolygon and LineString in PostGIS?
In general overlay operations such as ST_Split are liable to change vertices slightly, due to numeric rounding. So generally it is NOT the case that
A = ST_Union(ST_Split(A, B))
You could try comparing the areas of the original and the split geometries - they should be close (but will not be equal, due to the above behaviour).
6
The canonical definition of geometry validity is the OGC Simple Feature Access for SQL standard.
There is also the PostGIS documentation, although it is more descriptive than formal.
In short, repeated points are valid. This is defined implicitly in the above references. It certainly wouldn't hurt to make it explicit in the PostGIS doc.
1
You are correct that none of ST_Distance, ST_MaxDistance or ST_HausdorffDistance will meet your requirement. ST_HausdorffDistance is closest, but the problem with it is that it is symmetric, which means that a very large polygon will dominate the result. What you really need is the oriented Hausdorff Distance. JTS has an implementation of this, but ...
0
If the data is a MultiLineString with only one component, you can use ST_GeometryN to get the component LineString, and then run the functions needed on that.
0
This is an issue with the GEOS library that QGIS uses to compute buffers. And it's also an issue with the JTS library that GEOS is based on (and for which I'm the project lead).
I've logged a JTS issue for this. At the moment I don't have an idea for a fix.
Can you provide some more polygon data that fails?
1
In situations like this where the intent is to select existing geometry rather than construct new geometry it's preferable to avoid using overlay operations (like ST_Union) since they are less performant and can alter data slightly.
Instead, it's better to use spatial relationships. This is a classic case of "finding things which aren't related to other ...
1
Here is a link to an up to date (Sep 2019) file I created myself:
https://8n1.org/raw/15893/4597
Mirror: https://paste.ubuntu.com/p/vHbV2MTWfd/
2
Unnest is working when I try:
select id, coalesce(sub.level,0) maxlevel
from sometable
left join (
select distinct on (1)
unnest(ancestor) id2, level
from sometable
order by 1,2 DESC) sub
on sometable.id = sub.id2
0
I think the problem here is that you are probably doing a many to one query and so pandas is not automatically looking for the first match for each of the rows.
Here I sort the values in the district by the ed_name column
district.sort_values("ed_name",inplace=True)
Then I find all the matches and their index values:
indexes = district.ed_name....
4
When you SELECT a column FROM a table, the column value(s) are made available to functions, either per row, or as aggregates. Function results can be passed consecutively into enclosing functions.
In this case you would simply
SELECT ST_AsText(ST_Segmentize(geom::GEOGRAPHY, 50)) AS geom
FROM public.segments
;
Note that I use a cast (::) to GEOGRAPHY; ...
1
Use the DB Manager to create a Virtual Layer from your shapefile:
SELECT <col_1>,
...,
<col_n>,
MakePointZM(ST_X(geometry), ST_Y(geometry), ST_Z(geometry), <defaul_M_float_value> [, <SRID>]) AS geometry
FROM <loaded_shapefile_name>
;
You can easily change the ordinates to find the correct format.
Create ...
0
In this situation I would try the following :
Use a PostGres/Postgis Database you have access in (I.e can create table\ import data)
You can install a basic PostGres on your computer.
Import your shapefile as a new table using shp2pgsl in this database.
There you will see your data as a PostGresql table and infere your datatype.
If it's not a POINTZM or ...
2
Ideally, you would find the geometry type of the layer you are pasting into. Open the layer properties and click on the information tab. In the 'Information from provider' section, it will say what geometry type the layer is. I suspect it is PointM. You can then right click on your shapefile, save as, and set the SHPT to POINTM.
If, for whatever reason, ...
2
I don't use QGIS to import data but let me give you a working solution if you are able to use ogr2ogr.
ogr2ogr -f "PostgreSQL" "PG:host=serverhost user=username dbname=dbname password=password" yourshpfile.shp -lco GEOMETRY_NAME=the_geom -lco FID=gid -lco PRECISION=no -nlt PROMOTE_TO_MULTI -nln tablename -t_srs "EPSG:4326"
Add your own credentials and set ...
2
This has been answered several times. The question you are asking is how to explode the line into it's constituent segments. I found this solution here: see tilt's answer.
This worked when I tested it on my line table:
CREATE TABLE flood_def_segments as
WITH segments AS (
SELECT gid, ST_AsText(ST_MakeLine(lag((pt).geom, 1, NULL) OVER (PARTITION BY gid ...
0
As aicun said, it's a very complicated problem that require a specific implementation (OSRM, Graphopper, ...). You can also find API (like mapbox) that do that without needing to run the application. I think there is also a QGIS extension to that (I found Offline-MapMatching, maybe there is others)
If you want to stay in Postgis and azimuth maybe look into ...
2
Try this:
UPDATE polygontable AS p
SET length_of_lines = ST_Length(
ST_Intersection(
(SELECT ST_Union(geom) FROM linetable WHERE ST_Intersects(p.geom, geom)),
COALESCE(
ST_Difference(
p.geom,
...
0
Perhaps extract the individual points using ST_DumpPoints, and then connect them into a single LineString using ST_MakeLine(ST_Collect(pts)).
4
For get all the visited grid cells in order, even if the grid cell is revisited, I wrote the SQL query below :
SELECT tr.tr_id,
ce.grid_id
FROM cells ce, traj tr
ORDER BY ST_LineLocatePoint(
tr.traj_path,
ST_CENTROID(
(ST_DUMP(
ST_Intersection(ce.coordinates, tr.traj_path)
)).geom
)
);
Explanations :
The big part is ...
2
This became convoluted but it seems to work. I use dumprings to create lines of the polygons, use these line to split line input table, give id to each new line, join these to polygons by st_within (had to use st_line_substring to shorten the lines for some reason all would not fit within when they should) sort by rank and select distinct on id of split ...
2
I would go with...
SELECT Box2D(geom) as bounds,
ST_YMax(bounds) as ymax,
ST_YMin(bounds) as ymin,
ST_XMax(bounds) as xmax,
ST_XMin(bounds) as xmin
FROM nation_shapes WHERE (condition)
...as PostgreSQL's SQL engine and query planner is smart enough to figure things out and not duplicate work.
If you want to not return the bbox from ...
3
You get an entry for each voter because you are grouping on the voter column as well, which results in groups that are all single rows. And you cannot include voter_name in the query unless you either aggregate on the column or build groups that include the column, which means that you have to add one more query level to achieve your objective.
Here are a ...
1
So,
If anyone has problems resolving this issue, proceed as follows:
my source geodata is a polygon called poly_extent and administrative polygons without holes called adm_polygons see Figure 1.
Run the script
`
WITH
ta as (SELECT (ST_DumpPoints(geom)).geom FROM adm_polygons),
tb as (SELECT ((ST_Dump(ST_VoronoiPolygons(ST_Collect(geom)))).geom) ...
0
I had the same problem and I could fix it from
Geometry tools < "multiple parts to unique parts".
Easy and smooth...
0
An alternative to using evil eval() would be my PHP Shapefile library, that can read and write any ESRI Shapefile natively in PHP.
I (and many others, actually) have been using it for this very same scope, loading tons of Shapefiles into PostgreSQL, thanks to the WKT output provided by the library and PostGIS ST_GeomFromText() function.
It is a free, open ...
0
I took your code and get a closer look to it.
If you add points in your linestring without changing it (by adding the middle of the linestring for example)
It works well, you get the 0.5, 0.5, 1 intersection + points you add.
SELECT ST_AsText(ST_3DIntersection(ST_GeomFromText('LINESTRING Z (0.5 0.5 0, 0.5 0.5 0.2, 0.5 0.5 0.8, 0.5 0.5 2)'), "cube")) as "...
0
EPSG:4979 is the same as EPSG:4326, only with a third axis for the height.
PostGIS does not care that the CRS defines a third axis (the PROJ.4 text does not even mention it); it is always possible to use Z coordinates, regardless of how the CRS is defined.
So all reprojections will work, with the same accuracy as the normal EPSG:4326 (WGS84 lat/lon).
Too ...
0
It sounds like a foreign data wrapper may work, but I'm not familiar with MongoDB to know preciselythe best approach. I assume you can make an ODBC connection to the MongoDB with little problem.
I've used ODBC and ogr_fdw (https://github.com/pramsey/pgsql-ogr-fdw) to connect to FireBird tables with X, Y columns (no spatial capability in FireBird, but we had ...
2
If I correctly understood the translation of your question, try running this script:
If these are horizontal lines, then:
create table line_to_poly as
WITH closed_contours AS (
SELECT ((ST_Dump(ST_Polygonize(geom))).geom)
FROM sourse_table)
SELECT (ST_Dump(ST_Union(geom))).geom FROM closed_contours
If polygons, then:
create table un_poly as
...
1
You could use ST_Union to generate one big multipolygon and then ST_Dump to separate its component polygons into separate records. If you then needed to get attributes from the original data you could join based on geometry.
0
Yes, you will be able to use PostGIS with RDS. See Amazon's user guide on Working with PostGIS.
It appears to be the full, unmodified extension, so I would assume it operates just like a non-cloud installation. You'll still need a geometry column in order to run spatial queries.
0
I can't fit all of this in a comment, so I'm adding it as an addendum to @tilt's answer.
A working example using st_triangulate2dz to reproduce the constrained Delaunay triangulation (CDT) example on the Triangle website:
WITH a_collection_to_triangulate AS
(SELECT st_geometryfromtext('MultiPolygon (((0.8 -0.7924, 0.5792 -0.7924, 0.5792 -0.7764, 0.6216 -0....
0
The third field should be similar to what you can get with:
select encode(ST_AsEWKB(geom),'hex')
So it should be ewkb behind, displayed in hexadecimal. I find it not really easy to generate and pass it between computers, and especially to debug.
You can find library to use it in different languages like python with plpygis
Personnally, I prefer to import ...
Top 50 recent answers are included
