New answers tagged postgresql
0
If you have a column to store point geometry in your table e.g. "geom", then you can use the ST_MakePoint function. I suppose you have 2D geometry and you have the latitude and longitude in "lat" and "lon" column as decimal degrees. I supposed latitudes and longitudes are in WGS84 (SRID=4326). The following SQL command will update geom column:
UPDATE ...
1
If you were using SQL, you could use ST_DumpPoints to get at the points.
But inside an extension, you need a more direct access to the geometry object. The internal storage format used by PostGIS changes between versions, so you cannot use it unless you put your code into the PostGIS library (if that is even possible in your system). So the best method to ...
1
Solved it by overwriting my existing table with:
ogr2ogr -overwrite -f PostgreSQL "PG:user=postgres password=##### dbname=geodb host=localhost port=5432 active_schema=public" de_grid_etrs89_utrm32_100m.gpkg -lco LAUNDER=no
0
You can extract coordianates from a geometry column quite easily:
For x:
st_x(geometry_column)
For y:
st_y(geometry_column)
Documentation:
https://postgis.net/docs/ST_X.html
https://postgis.net/docs/ST_Y.html
1
You are correctly creating a functional (spatial) index right there, and it shows: one order of magnitude less execution time.
There may be subtle ways to coerce the planner to go for a more direct index lookup, but, assuming your data structure, none will have an improvement as significant to execution time as what you get from the current setup.
0
Okay 2020
Ubuntu 18.04
Postgresql 10
postgis 2.5
pgadmin3 (written in php?) pgamdin4 seems to be python so havent figured out how to apply it to that yet.
to install the shapeloader
sudo apt install postgis-gui
sudo vim /usr/share/pgadmin3/plugins.d/plugins.ini
then add this to the end of plugins.ini
;
; pgShapeLoader (Linux):
;
Title=PostGIS ...
0
Most likely the solution is around your possible solution #2.
If you don't modify your postgres.conf, PostgreSQL/PostGIS won't accept connections outside of localhost. If adding listen addresses = "*" to your conf seems unsafe or hacky, you can add your own IP instead, if it's somewhat static.
Here are few different options on how to locate your postgres....
0
I did some extensive search on the web but I think there is no 'quick fix' I think. So I made my cleanPolygon function. And that works very well.
It implements a two step solution.
Inputs:
geometry,
surrounding geometry
bordersnap precision
pointsnapprecision
Step A)
Go from vertex to vertex and check if there is a *surrounding point ‘in between’ with a ...
0
You're very close. The only issue I see with your code is the PG_OUTPUT variable, which should actually be a URI like this:
uri = 'postgresql://user:pass@localhost:5432?dbname=my_db&schema=my_schema&project=my_project'
If you have the URI in that way, you can just call:
QgsProject.instance().write(uri)
And now your project will be stored in ...
6
You can load a project from a database storage (e.g., PostgreSQL or GeoPackage) in this way:
QgsProject.instance().read(uri)
Where uri is the file name of the project, which you could get from:
QgsProject.instance().fileName()
Since we are dealing with projects stored in databases, the file name is actually a uri.
Sample uris:
Project in a PostgreSQL ...
7
ST_SetSRID will set the coordinate reference system of your geometry. This will allow PostGIS commands to understand how your grid will relate to other geometries.
Using ST_SetSRID is not essential, as long as all other geometries you may query against are known to be on the same grid. However, if you query against another table that has a SRID set, even if ...
7
With ST_SetSRID it is possible to define explicitly what the coordinates like (-122.079513,45.607703) mean: for example that they are WGS84 long-lat degrees https://epsg.io/4326 and not something else in some other system like https://epsg.io/3857.
As documented for example in https://postgis.net/docs/ST_GeogFromText.html EPSG:4326 is the default for ...
4
The PostGIS extension must be in the user search_path. Setting the search_path using ALTER DATABASE or ALTER ROLE will have effect at the next session only. To use the new path immediately, one would rather use SET search_path TO "$user", public, postgis, topology;
5
The error occurs because stitching two objects (literal string or column) requires a concatenation operator, like SELECT ST_PointFromText('POINT(' || locdata.loc_geo ||')', 4326) from locdata
But it won't work either because of the coma and the lat-long swap. Instead, you can read the coordinates, split them in an array, cast it to floats (or double), then ...
2
This is a fine thing to do, the easiest way to do it is with Astun's Loader, which is a free open source tool that is set up to take OS Master Map and feed it into PostGIS. It's so good that it is what OS use to load Master Map into PostGis.
A GML and KML loader written in Python that makes use of OGR 1.9. Source data can be in GML or KML format (...
0
@nbeuchat,
I found your idea interesting, at least from my point of view,
But function ST_Buffer() distorts the boundaries and so you don't mind my suggestion next,
run your script without ST_Buffer and see the result :-)...
SELECT (ST_MakePolygon(ST_ExteriorRing(ST_Union(
ST_Difference(ST_ConvexHull(ST_Union(geom)), ST_Collect(ST_Convexhull(geom))), ...
4
The doc on st_collect describes this situation:
If any of the input geometries are collections (Multi* or
GeometryCollection) ST_Collect returns a GeometryCollection (since
that is the only type which can contain nested collections). To
prevent this, use ST_Dump in a subquery to expand the input
collections to their atomic elements
So to use ...
2
As a raster is just a regular grid, you could use the raster to polygon functions to convert your raster to polygons and feed these to the function in the link you provided. If you use ST_DumpAsPolygons, adjoining cells with the same value will be merged, which means you will not have a regular grid, though the function in the link you provided copes with ...
0
Since you are computing distance between every pair, spatial indices don't help, the computation has to be done regardless of the index.
But since you only care about the minimum distance, you don't need every single pair. If you do want to use index, you need to use it in filter. How to do it? If you can guess the approximate minimum distance, add a filter ...
4
Updated methodology:
A more versatile, and a lot more performant way is to use an index driven (K)NN approach:
DROP TABLE IF EXISTS public.lengthdist;
CREATE TABLE public.lengthdist AS (
SELECT ln.id AS ln_id,
cat.orden AS orden,
ST_Distance(pts.geom, ln.geom),
ST_3DDistance(pts.geom, ln.geom)
FROM (
...
0
@ManyToOne
@JoinColumn(name = "Whatever your join column name is in your PGpath class")
private PGpath coordinates;
PGpath will also be an @Entity class with its own columns and column constraints, such as:
@Id
@Column(name = "Column name", nullable = false, length = 10)
private String myPrimaryKey;
3
GeoServer has not support for composite types. You can however publish data dataset in the classic relational way (separate table and foreign key) and use app-schema to explain GeoServer the relationship between the tables.
As a plan B, you can also implement support for composite types read wise, but it's not just reading them, you'll have to implement ...
1
You need to use WGS84 for lat long, so EPGS:4326. 3857 is web mercator.
ALTER TABLE "complete_details" ADD COLUMN geom geometry(Point, 4326);
UPDATE "complete_details" SET geom = ST_SetSRID(ST_MakePoint("LONGITUDE", "LATITUDE"), 4326);
1
The name of the function was changed in PostGIS 2.2 and I was looking at old docs. It is now called ST_DistanceSphere, which does work. (Docs: https://postgis.net/docs/ST_DistanceSphere.html)
2
Use ST_GeometryN to get the linestring out of the multilinestring, ST_PointN do get the first/second point, and ST_X/ST_Y to get the points' coordinates:
SELECT ST_X(ST_PointN(ST_GeometryN(tab.geom, 1), 1)) AS xi,
ST_Y(ST_PointN(ST_GeometryN(tab.geom, 1), 1)) AS yi,
ST_X(ST_PointN(ST_GeometryN(tab.geom, 1), 2)) AS xf,
ST_Y(ST_PointN(...
1
The convex hull idea worked pretty well but has the disadvantage of including parts of neighborhoods on the other sides of the source neighborhoods.
The following query builds on the convex hulls idea but fix the areas around the original neighborhoods.
SELECT ST_Buffer(ST_MakePolygon(ST_ExteriorRing(ST_Buffer(ST_Collect(ST_Difference(ST_ConvexHull(...
5
As mentioned in comments, a ST_ConvexHull is by far the easiest solution to generate your desired Polygon:
SELECT ST_ConvexHull(ST_Collect(shape)) AS geom
FROM subquery
;
ST_Collect is a lot more performant compared to ST_Union, and ST_ConvexHull will happily work with MULTI* geometries and GEOEMTRYCOLLECTIONs.
0
Not exactly what you want, but Close enough?
select st_convexhull(st_collect(geom)) as geom
from(
select geom, st_clusterdbscan(geom, eps:=25000, minpoints:=2) over () AS cid --polygons within 25000 map units are grouped together
from (
select * from ak_riks
where "kommunnamn" IN ('Askersund','Karlstad','Katrineholm','Karlskoga','Ludvika')) sub) cluster
...
0
Another thing you could do is to create a function to compute the tblc result (perhaps called ST_ClippedVoronoi). Note that the neatest way to do that is to make it a set-returning function.
Then you can implement the rest of the query as a regular SQL query using the resultants output from that function. This will make a simpler query, and will avoid ...
Top 50 recent answers are included
