New answers tagged postgis
0
The ST_Buffer solution already explained here is valid, but it can be improved in order to avoid the spikes (and the intersection step itself). You need to add a new parameter to the buffer mitre_limit.
SELECT st_intersection(
st_buffer(
st_buffer(geom, 1, 'join=mitre mitre_limit=1.0'
), -1, 'join=mitre mitre_limit=1.0'
...
0
You're messing up the order in the ST_Contains fuction.
ST_Contains(geometry geomA, geometry geomB):
Geometry A contains Geometry B if and only if no points of B lie in the exterior of A, and at least one point of the interior of B lies in the interior of A.
So your polygon and samplecol.the_geom need to switch places in the ST_Contains function.
for ...
0
You must use ST_3DIntersects. Which provides you the correct result on your sample query in your question:
SELECT ST_ASText(ST_3DIntersection(
ST_GeomFromText('LINESTRING( 1 1 1, 5 5 1)'),
ST_GeomFromText('LINESTRING( 4 1 10, 1 5 10)'
)));
Have a look at the link below:
It is available from PostGIS 2.0.0 onwards.
https://postgis.net/docs/...
1
The error message mentions " 0101000000A7E8482EFFC", with a trailing space, which makes the geometry invalid.
The dirty solution is to trim your data
SELECT ST_AsText(TRIM(GPS)) from schema.table;
with src as (select ' 0101000000A7E8482EFFC55B402063EE5A42064140'::text txt)
select st_asText(txt::geometry) from src;
ERROR: parse error - invalid geometry
...
1
the point is not a PostGIS type, but a native PostgresSQL type. The two are not compatible.
Creating a geometry from a string is one way. You can of course create a (PostGIS) point geometry via ST_Point or ST_MakePoint
select st_point(1,1);
0
SELECT
a.id,
b.id AS id2,
ST_MakeLine(a.geom, b.geom) as geom
FROM riverscale.points a, riverscale.points b
where a.id =1 and b.id=2
This will make the line between points with id’s 1 and 2
0
The select query is not correct to generate the rows.
Rewrite the select statement defining which ids of riverscale.points is start and end of Make Line function.
1
This works:
CREATE TABLE riverscale.test as
SELECT
a.id,
b.id AS id2,
ST_MakeLine(a.geom, b.geom) as geom
FROM riverscale.points a, riverscale.points b
ALTER TABLE riverscale.test
ALTER COLUMN geom TYPE geometry(LineString, 3763) USING ST_SetSRID(geom, 3763);
2
Building on what @JohnPowell said in the comments, you can make a join that is based on a spatial intersection. If you have a spatial index setup, this should have much better performance than a cross join. If you do not have a spatial indexes, you can create them directly in PostgreSQL using SQL or use the QGIS DB Manager to create them with a GUI.
Example ...
2
It is a geometry already.
What you have is not a table but the result of a query. You have at least 2 options:
1) create a new table with the output and load it in QGIS
CREATE TABLE test as
SELECT
a.id,
b.id AS id2,
ST_MakeLine(a.geom, b.geom) as geom
FROM riverscale.points a, riverscale.points b
2) load the tables in QGIS and query ...
4
In PostGIS, this can be done quite easily with a Cartesian product or CROSS JOIN in SQL, ie, joining a table on itself and using the 2nd form of ST_MakeLine. So, assuming a table called points with an id and a geometry called geom, this would look like:
SELECT
a.id,
b.id,
ST_MakeLine(a.geom, b.geom)
FROM points a, points b
WHERE a.id < ...
4
Tested on QGIS 2.18 and QGIS 3.4
I can suggest using a "Virtual Layer" through Layer > Add Layer > Add/Edit Virtual Layer...
Let's assume we have 10 features in "points" layer respectively, see image below.
With the following Queries, it is possible to achieve the lines between all possible connection of points excluding themselves.
SELECT p1.id ||...
1
If i'm correct you are trying to get the distance from each node in the network to each other node, correct?
(Image source)
Your subquery (SELECT array_agg(id) FROM ma_2po_4pgr ) returns each unique edge in the network, while'you want this query to return each unique node. To get all the unique nodes you have to select them form the source and target ...
0
As far as I know, PostGIS stores raster in bigger objects, not tile-wise. So your one line contains multiple raster cells, which you can see in any GIS program. Have you tried to use -t <a>x<b>, where a and b are the size of your raster cells? At least I've done that once and the corresponding value should be accessible with ST_Value. Check out ...
0
The easiest way I found is to cast the geometry type like this:
CREATE OR REPLACE VIEW view_waypAZ AS
SELECT polR.codeR, polR.nameR, ST_centroid(polR.geom)::geometry(Point,4326) as geom
FROM boundaryR as polR;
Check out PostGIS section 4.3.4. Manually Registering Geometry Columns in geometry_columns
https://postgis.net/docs/using_postgis_dbmanagement.html
0
You can also use ST_ClusterDBSCAN to cluster adjacent polygons together and then assign class by most frequent class in that cluster group:
--Add a temp column holding cluster id
alter table sometable add column cid int;
update sometable
set cid = sub.clusterid
from (SELECT *, ST_ClusterDBSCAN(geom, eps := 0, minpoints := 1) OVER () clusterid
FROM ...
0
I have the same problem as I cannot load the query into the map canvas.
In the python error console I get this
I have no
getExecutableSQLQuery()
to exchange and I don't use the "postgres" login for the DB.
I use QGIS 3.4 Madeira on a Mac.
Any ideas?
thx
0
I can only answer your first question, "Is it anyhow possible to collect data offline with a postgis database in QField? (Also with manaually synchronization)"
I created a sync package in QGIS and copied it on different tablets.
the input data to edit is a postgis table, in mode "offline editing".
Each tablet updates some of the objects in the offline ...
1
Hope this answers your question,
update your_table
set roadclass =sub.nearest_value
from
(
SELECT
p.idd as updating_id,b.id2 as nearest_id, b.roadclass as
nearest_value
FROM (
--selecting id and geom of null values
SELECT gid AS idd, geom
FROM your_table where roadclass is null
) p
--finding nearest polygon and its of the ...
0
As per my comment above:
Better use ST_DWithin for proximity searches:
UPDATE intersections AS g
SET facility_flag = EXISTS (
SELECT 1
FROM network_subset AS d
WHERE ST_DWithin(g.geom, d.geom, 100)
)::INT
;
This assumes your data is projected in a CRS with feet as unit.
Here, the Boolean result of EXISTS is (explicitly) cast to INT. ...
1
CREATE TABLE schema.polytable AS
SELECT column1, column2, ST_AsText(ST_CONVEXHULL(ST_COLLECT(geomcolumn))) AS newgeomcolumn
FROM schema.sourcetable
GROUP BY column1, column2
As suggested by someone else, this works in this scenario.
0
Method 4
1) Create two worksheets for stations by running the query:
SELECT * INTO stations_0 FROM stations;
SELECT * INTO stations_1 FROM stations;
2) Delete the stations that are included in the polygons:
DELETE FROM stations_0 as t1
WHERE EXISTS (SELECT 1 FROM provinces as t2
WHERE ST_Intersects(t2.geom, t1.geom));
and do not enter polygons:
DELETE ...
0
here is your solution:
CREATE SCHEMA gc;
CREATE EXTENSION postgis SCHEMA gc;
If you already installed the postGIS extension in your public schema and want to move in gc then write following code:
UPDATE pg_extension
SET extrelocatable = TRUE
WHERE extname = 'postgis';
ALTER EXTENSION postgis
SET SCHEMA gc;
2
Are you sure you're not misreading something?
SELECT ST_AsEWKT(st_transform(st_setsrid(st_makepoint(-79.3545864, 43.7094892), 4326), 26717))
"SRID=26717;POINT(632549.238889373 4840701.2020248)"
2
Try enforcing closed LineStrings by adding their ST_StartPoint to the end of the them:
SELECT ST_MakePolygon( ST_MakeValid( ST_AddPoint( shape. ST_StartPoint( shape ) ) ) ) AS shape
FROM asia_waterway_line
WHERE waterway ='riverbank'
;
If you want to make sure you are not duplicating points for when a LineString is already closed, run:
SELECT ...
0
So, I decided to forgo the use of raster2pgsql and trying to "fix" the datatable for my purposes. I instead wrote a python script to read a tif file and upload its information to PostGIS, using rasterio and psycopg2.
If anyone has a comment as to why this isn't a default behavior obtainable with raster2pgsql, I'm still curious. GeoTIFF, from my ...
1
Setting a different target schema is currently not supported by osm2pgsql. See this GitHub issue for details: https://github.com/openstreetmap/osm2pgsql/issues/768
1
You need to move the subquery outside of the function call:
SELECT ST_AsText(ST_cart2spr(X,Y,Z))
FROM (
SELECT ST_x(geom) As X, ST_y(geom) As Y, ST_z(geom) As Z
FROM (
SELECT (St_DumpPoints(geometry)).geom
FROM surface_geometry
JOIN building ON surface_geometry.id = building.id
WHERE geometry IS NOT NULL
AND cityobject_id = 95
...
0
You simply need to tell ogr2ogr what projection your data is in, as suggested by mrg. In my case it was -s_srs EPSG:3785.
You shouldn't have to redefine a standard projection in your database! This is not a clean solution and can have unexpected consequences later on.
0
The easiest way that I found is using OSMnx library in Python to upload the OSM map as a network in NetworkX (You can also use geopandas), once there is easy to link each of the points in the 'Points' Layer to the closer edge of the OSM layer and then use the steiner_tree algorithm to get the optimal tree, this is an example of a simple implementation of ...
0
When you (or whoever else) initialized the 3DCityDB instance, you didn't specify a spatial reference for it, which means that the data was saved with no SRID (i.e. set to 0). You will have to re-initialize the database with the proper SRID. Either delete the existing database or create a new one, then run the 3DCityDB setup scripts, but pay close attention ...
0
As I can't comment so that I will write it here.
You may have to provide the valid Spatial Reference Identifier SRID and an SRSName which is an OGC GML conformant CRS (coordinate reference system) definition identifier while importing the data to PostgreSQL Database.
Mark also the Extruded checkbox.
Perhaps this helps.
1
so after some manual checking here is a workin answer for people using OSM with the latest versions of the tools (OSM2pgsql and routing 2.62)
bascially way.target = route.node
SELECT * from ways join (
SELECT * from pgr_drivingDistance(
'SELECT gid as id
, source
, target
, length_m as cost FROM "ways" '
,...
5
my answer is based on: http://www.postgresqltutorial.com/creating-first-trigger-postgresql/ and https://stackoverflow.com/questions/39607334/postgres-trigger-creation-error-no-language-specified-sql-state-42p13
EDIT1: adjusting for ThingumaBob's comment
first you need to create a function to be used by your trigger action :
CREATE OR REPLACE FUNCTION ...
3
You can use ST_PixelAsPoints to get a geometry for each point and then to a "Cartesian join", ie, a full join between the points and the pixels, eg,
WITH pixels (x, y, geom) AS (
SELECT x, y, geom
FROM
(SELECT (ST_PixelAsPoints(rast, 1)).*
FROM rast_table
WHERE rid = 1
)
SELECT
gid, x, y, ST_Distance(points....
0
Use the following formula :
select ST_Length(ST_Transform(ST_GeomFromEWKT('SRID=4326;'||st_astext(the_geom)),26986)) as geolength from my_table;
0
The correct syntax is SELECT DISTINCT ON (geom, eventdate) geom, eventdate FROM datos WHERE orden='x'. By omitting on, Postgres thinks you are looking for a composite type, rather than essentially just a GROUP BY geom, evendate. – John Powell Jul 3 at 15:28
2
Using shp2geojson.js library it is possible to load ESRI shapefile and convert it to standard GeoJSON object that can then be used to create overlay in OpenLayers 3.
Below is simple example of such usage. Shapefile Mexico_and_US_Border.zip is loaded directly from https://opendata.arcgis.com/ site and then added as overlay over OSM map. The only critical ...
0
What worked for me was putting the files in my C or D disk. I guess it just for avoiding directories with strange caracteres as names or blank spaces.
3
You can use ST_FromGeoJson to convert the text to a geometry, then you would cast the result to the desired geography format. At last, you can use ST_Centroid
ex:
select st_AsText(st_geomFromGeoJson('{"type":"MultiLineString","coordinates":[[[-122.995882414,44.05766941700006],[-122.99818210099994,44.05768878400005]]]}'));
...
0
I think there is no issue with your secord query. It returns true or false for features which are in second table. But as your second table is empty the result from your inner select query is empty. So when you user where condition WHERE table_1.id = liste.name; no feature is updated. Instead you should have condition like WHERE table_1.id = liste.name or ...
1
To achieve something like:
http://ogc2.bgs.ac.uk/cgi-bin/UGA_ARGID/ows?SERVICE=WMS&VERSION=1.3.0&REQUEST=GetMap&TRANSPARENT=false&LAYERS=DAD_ITEMS,DAD_MAPS&CRS=EPSG:4326&STYLES=&WIDTH=1068&HEIGHT=952&BBOX=-13.962890625%2C19.604736328125%2C6.955078125%2C43.071533203125&format=application/openlayers&
Which is a ...
1
GeoServer will only show you tables that have a geometry column in them.
2
late to the party but not enforcing any spatial agg should be way quicker:
select min(ST_XMin(geom)) as l, min(ST_YMin(geom)) as b, max(ST_XMax(geom)) as r, max(ST_YMax(geom)) as t
from x
0
UPDATE1: although why you want to do this escapes me (joining with empty table) this query probably does what you want.
WITH empty as (SELECT T1.id AS name,
bool_or((ST_Contains(T2.geom, T1.geom) OR ST_Overlaps(T1.geom,T2.geom))) AS my_bool
FROM table_1 AS T1
CROSS JOIN table_2 AS T2
...
1
I would try to use ST_Line_Interpolate_Point.
Count number of points you need to place on a line,
and generate them by ST_Line_Interpolate_Point
with fraction equal to 0, 1/(n-1), 2/(n-1), ... 1.
0
I suppose your observations are point geometry. If that's the case you can use ST_ClosestPoint to find the closest point on line and plot them on line itself.
SELECT ST_ClosestPoint(
ST_GeomFromText('MULTILINESTRING ((10 10, 40 50), (20 20, 50 20, 50 60, 20 20))'),
ST_GeomFromText('POINT(20 40)')
);
3
Just like with the COUNT(*), the SELECT can utilize (almost) every other aggregate function, keeping the reference to the grouped rows defined by the GROUP BY.
Not directly obvious: in your first query you implicitly GROUP BY ST_SnapToGrid(...) already, by using the column expression alias grid.
With that in mind, just run this very similar query:
SELECT ...
3
would something like this do the job?
UPDATE1: used st_crosses instead of st_intersects and not st_equals and created an index for the geometry
UPDATE2: instead of calculating a huge multipoint, lets calculate one multipoint per join_id and let's split only the line with this join_id. Also, if a particular join_id has only one segment bring it anyways
...
1
PostgreSQL/PostGIS implement the SQL/MM specification, so just write it in proper SQL:
SELECT *
FROM my_border_table t1
JOIN my_polygon_table t2 ON ST_Intersects(t1.geoloc, t2.geom)
WHERE ST_Dimension(t1.geoloc) = 2
AND ST_Dimension(t2.geom ) = 2;
-- or:
-- ST_GeometryType(t1.geoloc) IN ('ST_Polygon', 'ST_MultiPolygon')
-- AND ST_GeometryType(t2....
Top 50 recent answers are included
