New answers tagged postgresql
2
Best would be to use a TRIGGER to save any INSERT, UPDATE or DELETE to an audit table. Then you can "rollback" to any state in the original tables for any time.
From this page:
CREATE OR REPLACE FUNCTION audit.if_modified_func() RETURNS TRIGGER AS $body$
DECLARE
v_old_data TEXT;
v_new_data TEXT;
BEGIN
/* If this actually for real auditing (...
1
Looks like I've hit a known QGis bug.
If we use authentication on the PostGIS connection (that is, store the password securely), the connection using SSL fails. If instead I remove my authentication and type the user/password every time, the connection works.
Here is the link to the bug report:
https://github.com/qgis/QGIS/issues/25569
or
https://issues....
0
I think it should looks like this (buffer of 200):
SELECT
pt1.id_pt,
count(pt2.id) as nb_pt_in_buffer
FROM
points pt1
LEFT JOIN
points pt2
ON
ST_Intersects(pt2.geom, ST_Buffer(pt1.geom, 200))
GROUP BY pt1.id_pt
But your problem looks like a clustering problem, so maybe you should take a look to the ST_ClusterDBScan function ? It ...
2
You could wrap everything in another query:
SELECT * FROM (select ...original query...) GROUP BY geom;
But it would be easier to put a filter clause on the count:
SELECT geom,
count(*) FILTER (WHERE var = 'a'),
count(*) FILTER (WHERE var = 'b'),
count(*) FILTER (WHERE var = 'c')
FROM mytbl
GROUP BY geom;
1
I've found solution with '||quote_literal(variable)||'. For my example :
DO $$
DECLARE
url character := '193.00.00.00';
pt character := '5432';
db character := 'bd_name';
usr character := 'user_name';
pwd character := 'password';
BEGIN
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
EXECUTE 'CREATE SERVER foreign_bd_ign
...
1
You can use EXECUTE command with a concatenated string (with USING like @D_C said, or like the following), but don't forget these parameters need to be encapsulated with '', so you need to escape with \' if you are already in a string:
EXECUTE 'CREATE SERVER foreign_srv
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host \''||url||'\', port \''||pt||'\', dbname ...
-1
I had to do a proof of concept for another app so put together this repo which, in part uses the answers from here. Hopefully helps getting started with this stuff - find it here:
pg-us-census-poc
3
What if you tried the EXECUTE command:
EXECUTE 'CREATE SERVER foreign_srv
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host $1, port $2, dbname $3)' USING url, pt, db;
https://www.postgresql.org/docs/9.1/plpgsql-statements.html
There are a bunch of different ways this execute command can be used. Variations of format, %s, %L, etc.. You'll have to look up ...
0
There is a new PostGIS release out today (PostGIS 3.0.0rc1) that works with PostgreSQL 9.5-12 and GEOS >= 3.6: http://postgis.net/2019/10/08/postgis-3.0.0rc1/
(Admittedly I have not yet succeeded in adding it to my own PostgreSQL 12 database on Windows 10, which is how I landed here on this thread.)
3
By default, osm2pgsql transforms the raw geographic coordinates (EPSG:4326 - WGS84) to EPSG:3857; to populate the geom column in your gps_coordinates table accordingly (i.e. defined as GEOMETRY(POINT, 3857)), run
UPDATE gps_coordinates
SET geom = ST_Transform(ST_SetSRID(ST_MakePoint(longitude, latitude), 4326), 3857)
;
After that, running
SELECT a.id,
...
3
The best fit bearing should be sufficiently approximated as the average azimuth over the sequence:
SELECT AVG(azm) AS bearing
FROM (
SELECT degrees(ST_Azimuth(geom, LEAD(geom) OVER(ORDER BY <sequence_id>))) AS azm
FROM <your_table>
) q;
Alternatively, you can easily reproduce the approximation as described in the linked answer with ...
0
A SQL query will do the trick, I think?
ALTER TABLE name
SET SCHEMA new_schema
In my opinion, the spatial_ref_sys table must remain in the public schema.
0
You could tediously extract those numbers from the string using PostgreSQLs regex functions, but I suggest to simply add a geometry column and update within a DO block using EXECUTE:
ALTER TABLE <your_table>
ADD COLUMN geom GEOMETRY(LINESTRING, 4326)
;
DO
$$
DECLARE r RECORD;
BEGIN
FOR r IN SELECT <id>, <statements> FROM <...
2
The documentation for ST_Distance states: For geometry type returns the minimum 2D Cartesian distance between two geometries in projected units (spatial ref units).
Therefore, it is not returning meters like you assumed, it is returning degrees. Depending on your latitude, one degree is equal to around 111 KM!
https://en.wikipedia.org/wiki/...
2
Things to consider:
(K)NN searches are costly, and the key for performance is the use of the spatial index; any form of manipulation of the geometries will render that index useless.
Don't transform the geometries of the running table on-the-fly.
Spherical/Pseudo/Web Mercator is among the most uselss 'projections' there are when it comes to any form of ...
0
https://github.com/mapbox/awesome-vector-tiles#cli-utilities - a curated list of awesome things related to Vector Tiles (CLI, Servers, Clients, etc.)
You can generate(serve) VEctor Tiles directly from PostgreSQL database (+PostGIS) using 't-rex' (https://github.com/t-rex-tileserver/t-rex) or 'tegola' (https://github.com/go-spatial/tegola) servers
0
Just adding this as an addendum to the accepted answer:
It looks like ST_AsMVT (https://postgis.net/docs/ST_AsMVT.html) actually does support outputting more than just geometries - maybe that's new, I don't know.
From the docs:
The Mapbox Vector Tile format can store features with a different set of attributes per feature. To make use of this feature ...
0
I use pg Admin 4 and I have got the shapefile DBF loader that works perfectly and I access it through the start menu. Whereas with pg Admin 3 it is under plugin on the GUI but not with pg admin 4. pg Admin 4 is not trash, it is perfect and works perfectly. I use both 3 and 4. Please ensure that you install the software correctly with all the necessary tools.
1
this is my attempt without having the data and under the assumption that you do not care about any attributes at all from tables 2 or 3.
merge the geometries of t2 and t3
create cluster geometries where there are overlapping geometries to reduce complexity
create an index on this layer
run a basic st_dwithin on the lines to clustered polygons made of table ...
0
For future work and to improve on work efficiency, I suggest you link your PostgreSQL database to QGIS desktop, this way, when you do changes to your data in the database it gets updated immediately in the database instead of deleting the table and reimporting it or exporting to Postgres via QGIS.
0
Wilhelm Berg and I ran into this problem recently, and he found that using tippecanoe-json-tool to convert the large geojson to a line-delimited geojson made it digestible for ogr2ogr:
tippecanoe-json-tool my_large_file.geojson > my_large_file.ldjson
ogr2ogr can then read the .ldjson file using the new GeoJSONSeq driver, which should prevent OOM errors:
...
2
DXF format does not really support multipoint geometry type and therefore GDAL DXF driver https://gdal.org/drivers/vector/dxf.html can't handle them. FME does support multipoints with a workaround by storing them as blocks
https://docs.safe.com/fme/2018.1/html/FME_Desktop_Documentation/FME_ReadersWriters/acad/Geometry-Support-acad.htm
What you can do with ...
0
I believe the problem comes from using the ST_Line_Merge function which can, apart from a LINESTRING, also return a MULTILINESTRING (if the line segments are not adjacent to one another), and that is not compatible with ST_Line_Interpolate_Point which then throws an error.
1
I believe you are probably looking for geopandas-postgis (https://github.com/awburgess/geopandas-postgis; https://pypi.org/project/geopandas-postgis/)
import geopandas as gpd
from sqlalchemy import create_engine
import geopandas_postgis
engine = create_engine("postgresql+psycopg2://postgres:postgres/localhost:test")
gdf.postgis.to_postgis(con=engine, ...
0
Because you have already set the configuration file, I suggest you establish the Transmission Control Protocol (TCP). Please follow this tutorial.
https://www.youtube.com/watch?v=4IhfbxhoAn8
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(...
0
Select "DepTime2", "FlightDate" || ' ' || "DepTime2"
,
Select TO_TIMESTAMP('20191001' || ' ' || '18:33','YYYYMMDD HH24:MI')
from DEP1."FactItinerary"
limit 10;
EXAMPLE:
Select TO_TIMESTAMP('20191001' || ' ' || '18:33','YYYYMMDD HH24:MI')
Top 50 recent answers are included
