New answers tagged postgresql
-1
just copy these two .dlls ("libeay32.dll" and "ssleay32.dll") from bin/postgisgui folder and paste them in bin folder.
2
So this worked for me but no promises because I don't really know what I did I just followed some stuff I found else where. Now I'm on win 10 installing PostgreSQL 11.5 and postgis 2.5 (installed through the native application stack builder) all 64bit and I just did a new install of them all.
Then I followed the instructions here and copped the dll files "...
1
Try:
buffer_gdf = gpd.GeoDataFrame(geometry=buffer_polygs)
Another way using from_postgis copied from the answer to Geopandas PostGIS connection:
import geopandas as gpd
import psycopg2 # (if it is postgres/postgis)
con = psycopg2.connect(database="your database", user="user", password="password",
host="your host")
sql = "select geom, x,y,z from ...
0
I've solved my problem. I had to move away from GeoTools and write a PostGIS query, because GeoTools doesn't appear have the features needed to do this successfully.
Here's the query:
SELECT intersection_query.census_block_group_gid, ST_Multi(ST_Union(intersection_query.geom)) AS geom
FROM (
SELECT md_census_block_groups.gid AS census_block_group_gid, ...
0
I have thought about listing tables without geometry which should also be helpful:
select tablename from pg_tables where schemaname='yourschemaname'
except
(select f_table_name from geometry_columns where f_table_schema = 'yourschemaname');
0
Try:
SELECT *
FROM points123
WHERE ST_Within(geom, ST_GeomFromText('POLYGON((lng1 lat1, lng2 lat2, lng3 lat3,...,lng1 lat1))',3857)))
1
In general, you'll find that geocoders have to work with their own database, and cannot be linked to an existing one. There are too many combinations of configuration parameters, table schemas, etc to handle.
However, exporting data into a format that can be imported into a geocoder's database is usually possible.
For Pelias, the csv-importer was designed ...
1
No time for more improving or testing, but: for a single, more generic recursive term, and possibly better performance, try
WITH RECURSIVE
params AS ( -- convenience variables for testing parameters
SELECT 10 AS max_size, -- max. cluster size
1 AS max_points, -- 'max_points' parameter
...
1
I have been able to work around the limitation by "pre-computing" the values for eps and inferring reasonable values of the other subqueries which where previously referring to the recursive CTE.
Note that the new solution may create clusters larger than wanted (5000 in the query below) if you run-out of "pre-computed" values. This helps ensure that the ...
0
How about something like this..?
DECLARE @sql NVARCHAR(max)
SELECT @sql = N' '+ String_agg(Concat(N'
UPDATE ', CONVERT(NVARCHAR(max), Quotename(col_table_name)), N'
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];'), N'' + CHAR(13) + CHAR(13))
FROM information_schema.tables
WHERE [condition]
EXEC (@sql)
Once you get ...
3
You can write a small Postgresql loop to iterate over all the tables you want.
With this query, you get all tables.
SELECT table_name, table_schema FROM information_schema.tables
Probably you don't want all tables. In the loop below, you can uncomment the WHERE clause just to select the tables you want.
The loop looks like this:
DO $do$
DECLARE
sch ...
0
To solve your question, use the following syntax:
SRID = 4326; POINT(0 0)
with respect, :-)
1
To change the inserted data, you can use an INSTEAD OF trigger on a view:
CREATE VIEW bar AS
SELECT ST_AsText(geom) AS geom FROM foo;
CREATE FUNCTION insert_bar() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO foo(geom) VALUES('SRID=4326;' || NEW.geom);
RETURN NEW;
END;
$$;
CREATE TRIGGER insert_bar
INSTEAD OF INSERT ON bar
FOR EACH ROW
...
0
It is also possible you do not have postGIS properly installed on your database instance. Try running CREATE EXTENSION postgis; and then try running your original query again. I hope this helps.
0
I found the solution, the source from which I had received Table_2 was supposed to give it in the SRID 2154 but it did in 4326. I had then imported the layer with the SRID as 2154 whereas it wasn't.
After reprojection of the layer and re-importation under Postgis, the initial query worked.
ANSWER: ALWAYS CHECK SRID EVEN WHEN IT IS ASSUMED TO BE KNOWN
0
Better use the db manager once your connexion is set as you will see all of your tables (non geometric one too) and you just have to drag them to the toc. You will not have to select manually the fields mentionned by Steven (sorry, just register 2 days ago so i cannot post this as a comment).
2
Install the PostGIS Addons and have a look at ST_ExtractToRaster() with the COUNT_OF_POINTS method. Should go like this after you created a reference raster:
CREATE TABLE count_of_point_coverage AS
SELECT ST_ExtractToRaster(
ST_AddBand(ST_MakeEmptyRaster(rast), '32BF'::text, -9999, -9999),
'public',
'sampledata',
'geom'...
0
Unfortunately, CARTO Builder only supports aggregation metrics (max, min, avg, count) based on single columns. That metric is generated from the features of the (analysis) layer node that points that particular widget but also filtered by the selected categories and numerical ranges of other category and histogram widgets pointing to it.
If you do not want ...
2
You can have many joins, so this should just work:
SELECT
utmid,
count(points1.geom1) AS points1,
count(points2.geom1) AS points2
FROM utm
LEFT JOIN points1
ON ST_Contains(utm.geom, points1.geom1)
LEFT JOIN points2
ON ST_Contains(utm.geom, points2.geom1)
GROUP BY utmid;
I highly recommend not naming fields in your tables after the table name....
0
I would recommend using this documentation Backup and Restore
A powerful, but user-friendly Backup and Restore tool provides an easy way to use pg_dump, pg_dumpall, and pg_restore to take backups and create copies of databases or database objects for use in a development environment.
Using the pg_dump utility, pgAdmin provides an easy way to create a ...
1
session_user is the user that established the connection. current_user is the explicitly set role
ex: connect as myUser -> session_user == current_user == "myUser"
set role myRole; -> session_user == "myUser"; current_user == "myRole"
Both can be conveniently combined in a track-change trigger:
IF session_user = current_user THEN
NEW.UPDATE_USER :=...
1
Maybe you can try an iterative approach:
You first use ST_ClusterDBSCAN with a big eps and a small minpoints, and then you isolate the points that are in a cluster too big for you, for exemple using the radius of the bounding circle (general idea, not tested):
sqrt(ST_Area(ST_MinimumBoundingCircle(ST_Collect(points)))/pi) > your_threshold group by ...
4
I think it's actually a really complicated problem.
What I would do is first dump the geometries and then try to select the 2 extreme lines then simply use st_distance.
To select the 2 extreme, if they are ordered it's easy but if they are not, if you are sure they have the same length try order by ST_XMin, ST_YMin and take the first and the last one.
...
4
You can compute a compactness test in a query, but you really don't want to. Here's an example of a small (100k row) table:
DROP TABLE IF EXISTS example1
;
CREATE TABLE example1 (
idcol serial NOT NULL,
isCircle char(1) NULL,
geomcol geometry NULL,
CONSTRAINT example1_pk PRIMARY KEY (idcol),
CONSTRAINT ...
5
You could try calculating a compactness score for your geometries to see if they are a circle. Something like the Polsby-Popper test will calculate a ratio between 1 and 0, 1 being a perfect circle and any other geometric shape will have a smaller ratio.
4 * pi() * (area/(perimeter^2))
If you are working with perfect circles you can select anything with a ...
1
What you have got wrong is CRS specification when reading features from GeoJSON with readFeatures method. This method has two CRS options: dataProjection tells projection of GeoJSON data and featureProjection tells projection of generated features.
In your case dataProjection should be 'EPSG:26918' and featureProjection default 'EPSG:3857':
var features = ...
0
Using the combination of Sociabilis and JGH hints the problem solved as follow:
ogr2ogr -f "GeoJSON" out.json "PG:host=localhost dbname=test user=postgres password=x" -sql "select * from line."\Road_a\""
2
First of all, a geohash is easier to explain with referencee to points, but the logic can easily be extended to a grid, using two points, for opposite corners, similar to how ST_MakeBox2D works. A geohash in made up of interwoven bits, where each even bit represents increasing precision (powers of two in longitude) and each odd bit represents increasing ...
1
In Postgres, you can join and query the same layer: once for the "source", and one for the connected neighbors. Grouping by source ID will let you count the connected neighbors and will also open the door to aggregate functions.
A spatial index is required if you want a descent execution time.
SELECT a.id, count(*), string_agg(b.id::text,',') as neighbors
...
Top 50 recent answers are included
