New answers tagged postgresql
0
The problem is how you place coalesce function, this way it works:
select coalesce( (SELECT DISTINCT COUNT(pt.geom) OVER w sum_of_pt
FROM pt, poly
WHERE ST_Intersects(pt.geom, poly.geom)
WINDOW w AS (PARTITION BY poly.geom)),0) sum_of_points;
0
Try to use ST_GEOGFROM(FROM_HEX(...))1:
SELECT ST_GEOGFROM(FROM_HEX('01010000206A080000C88758C0E72B1441EE517105E8185A41')) AS WKB_format
or ST_GEOGFROMWKB(...)2:
SELECT ST_GEOGFROMWKB('01010000206A080000C88758C0E72B1441EE517105E8185A41') AS WKB_format
0
Specs: Bare metal - Intel(R) Xeon(R) CPU E5-2630 0 @ 2.30GHz (24 cores) 32GB RAM 1TB SATA
You don't specify what type of drive "1TB SATA" means. Is that a 1TB SATA SSD or HDD? If HDD, then you should definitely switch to SSD, preferably even NVMe. Many file or database operations in the rendering process require fast random access to disk, ...
1
The tile servers are hardcoded in web/pgadmin/static/js/sqleditor/geometry_viewer.js.
In an installed pgAdmin, you can find this file in a minified form in .../web/pgadmin/static/js/generated/sqleditor.js; search for "tile.openstreetmap.org".
If you cannot edit that file, then you might be able to configure your internal DNS to redirect a|b|c.tile....
4
Not directly, but you can construct a new geometry - or a geometry collection - containing the two geometries using st_collect
select st_collect('point(1 1)'::geometry, 'linestring(0 0, 0 1)'::geometry)
or more likely from two tables
select st_collect(a.geom,b.geom)
from a join b on ...
0
Here is the answer:
SELECT ST_X(location::geometry), ST_Y(location::geometry) FROM locations;
0
It seems to me that the problem is that you are using the QGIS API to perform the query, if you want to have a performance similar to PgAdmin you must implement the PostgreSQL engine in QGIS.
That is, establish a connection to the database and implement a SQL statement to the Database.
You can achieve this in several ways, using the QGIS API, specifically ...
1
Like said in comments, this query is particularly long because the planner does not consider it necessary to use a geographical index when executing the WHERE clause. Initially I thought it was either because the table had no index, or the statistics were not up to date, or because of the use of a CROSS JOIN. So I tested these queries on a large raster table ...
5
Ok. The generalized goal is to coalesce geometries A and B based on a condition.
I'll assume you know the IDs of the features you need to have fixed and that all rows in study_region_all have values in the geom field.
In that case, you can do:
WITH needsfixing as (
SELECT
a.id,
b.geom
FROM
study_regions_all a
INNER JOIN counties b on ST_INTERSECTS(a.geom,b....
2
Based on the commnt from @J. Monticolo I have created this that do exactely what I want.
I can't mark a comment as accepted answer, so I'll accept th ansver from JGH, which is similar, but came while I was coding.
-- draw lines from point-to-point, attributing with temporal and spatial distance... ver. 1.1
drop table if exists dmaais.sellegs cascade;
create ...
2
To get multiple segments for each ID, you can't group by ID.
Instead, you can use a window function to partition by ID, order by ddt and at last to access the following row
with src (id, dtt, geom) as (values (1,1,'point(1 1)'),(1,2,'point(1 2)'),(1,3,'point(1 3)'),(2,1,'point(2 1)'),(2,2,'point(2 2)'),(2,3,'point(1 3)'))
SELECT id, dtt,
st_asText(
...
9
Some of your data is not in 4269 as expected. Maybe it has already been converted to 102008 or else.
You can query all records that are out of bounds:
select * from data.studies_ln_reg_07
where abs(st_x(geom)) > 180 or abs(st_y(geom)>90;
example:
select st_transform(st_setsrid('point(575 90)'::geometry,4269),102008);
ERROR: transform: latitude or ...
9
Use UNION or UNION ALL, depending on whether you want duplicates. Example:
SELECT f1,f2,f3
FROM t1
UNION
SELECT f1,f2,f3
FROM t2
Assuming the structure is exact across all three tables, the schema you're using is public, the geometry field is called geom and fields are ordered the same way:
CREATE TABLE public.master AS
SELECT * FROM region_1
UNION
...
1
Your coordinates are already in srid 3857. To test this, I created a new table with a linestring in srid 4326. I then altered the geometry type to 3857:
alter table temp_delete alter column geom type geometry(linestringm, 3857) using st_transform(st_setsrid(geom,4326),3857);
I then attempted to re-alter the table geometry type to 3857 (same code as above),...
0
Try to use ogr2ogr with -forceNullable option.
1
You just need to add a line on you request to calculate a table with a line between node_dp."Geom" and east_node."Geom":
select
drops."DP Name",
drops."East Node Name",
node_dp."Geom" as dp_geom,
east_node."Geom" as eastnode_geom,
-- Line Creation
...
6
If you need to use meters, cast your geometry to geography in the ST_DWithin function:
SELECT *
FROM stores
WHERE ST_DWithin(location::geography, (ST_SetSRID(ST_MakePoint(12.5053379,55.7655287), 4326))::geography, 5000);
A side note: You can't use ST_SetSRID to transform coordinates. The point coordinates you have are in SRID 4326. What you could do,...
0
The used GeoTIFF file uses extendend tags 'Predictor'. However, GeoDjango or more precisely GDALRaster only supports tags in baseline format. Details here: https://www.loc.gov/preservation/digital/formats/content/tiff_tags.shtml
It works with the sample data here: https://docs.djangoproject.com/en/3.2/ref/contrib/gis/gdal/
1
Due to the complicated nature of views, they are often not "update-able". To make some views editable, you have to create an INSTEAD OF trigger that "ON UPDATE" for the view telling PostgreSQL explicitly what to do when you modify the results of a view. This of course must be completed in PostgreSQL not as a join in QGIS
1
Might I add here that if you have full permissions on the DB in question, you could join your data on QGIS, delete the existing table in the DB, and re-upload the data into a table by the same name, you could even drag and drop. This is usually how I accomplish tasks that require the processing of data that lives in a DB.
5
TL;DR: You can modify data physically present inside individual tables, but you cannot alter their columnar structure (or row counts) based on a join of multiple data sources.
Make sure you understand the conceptual basics of the data structures you are mentioning, and the implications of using them within a client application like QGIS.
PostgreSQL, as an ...
2
As soon as you save your edits in a PostGIS layer, the modifications will be committed to PostGIS, so that it will be visible in any other tools (e.g. pgAdmin).
Although, if you do a attribute join in QGIS, that is just a use of the data that will not be reflected back into the database. (remember, you can do an attribute join between any type of data ...
2
The problem was the size of the raster and not the size of the polygon (which was pretty obvious in hindsight)
To resolve this I sacrificed precision and rescaled the data which got me past this issue.
insert into srtmup select rid, st_rescale(rast,0.003) from srtm;
However though this helped with the st_intersection function I realized that this was ...
6
I wrote a set of functions to easily handle dividing lines by length and by segment count, both in PL/pgSQL as simple .sql files, and C as PostGIS core add-ons (needs you to build PostGIS yourself) and I'm shamelessly going to promote it's usage here.
The following assumes to have one of both options installed - with the PL/pgSQL option being a trivially ...
3
PostGIS has a small but powerful set of Linear Referencing functions to work with trajectories, utilizing the full potential of M values:
interpolating an isolated event (a MULTIPOINT M, or GEOMETRYCOLLECTION M if multiple events can be isolated) on the trajectory (LINESTRING M) at time T:
SELECT ST_LocateAlong(s.segment, <T>) AS event
FROM segments ...
1
The default network connection timeout is 60 seconds, and can be changed in the Settings:
I think DB connections also honour this value.
2
I have completely overlooked that a LineString is not allowed to branch off. Hence, all clear now.
Top 50 recent answers are included