New answers tagged postgresql
0
Search for closed LINESTRING geometries in your network:
SELECT ST_IsClosed(geom), geom
FROM line_network_table
WHERE ST_IsClosed(geom) = 'true';
ST_IsClosed(geometry g);
Description: Returns TRUE if the LINESTRING's start and end points are
coincident.
0
I solve the same problem by:
Change the METHOD from "md5" to "trust" in pg_Hba.conf
Download the last "PostgreSQL JDBC 4.2 Driver, 42.2.20" from
https://jdbc.postgresql.org/download.html and paste in C:\Program Files (x86)\GeoServer 2.15.1\lib
I´m not sure, but it is supposed to have lower security if we change the METHOD, so ...
1
For MS4W users on Windows, I've created the documentation (https://ms4w.com/README_INSTALL.html#fastcgi) for FastCGI, which gives more specific examples, such as:
<IfModule fcgid_module>
FcgidMinProcessesPerClass 0
FcgidIdleScanInterval 1
FcgidProcessLifeTime 10
</IfModule>
and also points to the mod_fcgid page where each of ...
0
The query plan shows that for each row to be updated in pt (p in the plan), another seq scan is done on the other table s (v in the plan), which basically means that 18M * 3M = 54M rows are scanned, which has to take some time.
Add an index on the filtering key on the 2nd table (s / v), and don't forget to run vacuum analyze on it before re-running the query
3
Not sure what you tried but before going through the programming, you could try selecting a range of layers in the "Data Source Manager" for your database.
Using a click to start with Shift key and clicking again later in the list select a range of tables/layers (PostGIS/QGIS perspectives)
For a programming perspective, you can do the following:
# ...
0
It took me a while to figure this out and I want to share the solution so that others can real the benefits. Indeed, @chrismarx mentioned the basis for the answer in the last comment above: CREATE TABLE AS
Here is the process that has worked out very well for me:
Verify that the base query is parallel-compliant.
EXPLAIN
-- Insert query
SELECT
...
FROM...
;
...
2
The query is doing an implicit cross join between v and a, then you join a to n with a ON condition between n and v, which is invalid (the implicit cross join with v will occur after the explicit joins between a and n)
If you do want to keep the implicit cross join, you would have to swap v and a:
SELECT v.voronoipoly, n.nomcomar
from altcamp a, voronoi v
...
3
PostGIS internally stores geometry data as binary objects which are optimized for scanning and processing. Geometry objects include the SRID.
WKT, EWKT, WKB and EWKB are just representations for external use. As per the OGC spec WKT and WKB do not include the SRID. EWKT and EWKB do include the SRID, so they can be used as full-fidelity representations.
2
In ogr2ogr the -gt option is the correct and only way to control the size of the transactions https://gdal.org/programs/ogr2ogr.html#cmdoption-ogr2ogr-gt
-gt n
Group n features per transaction (default 100 000). Increase the value for better performance when writing into DBMS drivers that have
transaction support. n can be set to unlimited to load the data ...
6
PostGIS implements type specific meta information (geometry type & SRID) via the PostgreSQL typemod system catalogues; a typemod adds specific constraints to (base) types, and cascades their restrictions to e.g. columns. Without specifically adding these constraints to the extension types (GEOMETRY/GEOGRAPHY), their defaults (SRID = 0) will be assumed.
...
4
You need to change 'uiFileContent' to ''. The role of uiFileContent is to provide the name of the Qt Designer UI file if you are using a custom form (see https://github.com/qgis/QGIS/blob/2d1aa68f0d044f2aced7ebeca8d2fa6b754ac970/src/gui/vector/qgsvectorlayersavestyledialog.cpp#L171 to see from code where my affirmation comes from). If you don't have one, the ...
5
The >0 distance returned by the query is actually correct.
Let's remember that when using the type geometry, distances are computed using a straight line while with the type geography, the great circle line is used.
So, your data is in geometry and you compute the closest point using a straight line (between the start and end point of the line). The ...
2
If you segmentize (or densify) your line then it will work better.
SELECT
ST_Segmentize(ST_MakeLine(ST_MakePoint (- 86.26725, 34.32582) , ST_MakePoint (- 86.14040, 34.39281)),.01) AS line
, ST_MakePoint (- 86.19219 , 34.36546) AS point
)
select
st_distance(point::geography, line::geography) AS i
, st_distance(st_closestpoint(line, point)::...
0
Following from @geozelot's champion answer, (I write as an answer, not a comment, so as to format the code block) this worked for me;
UPDATE schema.table2 AS itp
SET ogz = round((
SELECT SUM(z/d)/SUM(1/d)
FROM (
SELECT smpl.ogz as z,
COALESCE(ST_Distance(itp.geom, smpl.geom)^1.5,1)+1 AS d
FROM schema.table1 AS smpl
...
1
To fix this problem, check "Beta: Use Unicode utf8 for worldwide language support " box in control panel ->region, administrator ->, language fornon unicode, which unfortunately does not exist in older Windows updates.
2
If I understand your question right: "so it returns only the smallest polygon nearest the point", you can ORDER BY the size of the polygon (or another criterion you choose) and then return only the first record:
select pt.point_id, ply.polygon_id,ply.admin_level
from point_table pt
join polygon_table as ply
on ST_WITHIN(pt.geometry, ply.geometry)
...
4
You are passing in the metric length ST_Length(geom::GEOGRAPHY) of each edge E as both cost & reverse_cost - rendering it equal to to the undirected counterpart.
pgRouting realizes ordered graph restrictions via the [reverse_]cost parameters; generally speaking, while an undirected graph considers only a single cost per edge E, a directed graph treats ...
0
Please see the related Issue and the solution. With help of the anwer at
https://stackoverflow.com/questions/67008997/query-all-primary-roads-from-postgis-database-filled-with-osm-data-within-a-boun?noredirect=1#comment118462981_67008997
I was able to optimize my PostGIS Query. It is consistently faster for this opration then Overpass.
0
Old question, but if you are using the OS postcode point data (Code-point Open), and you have it in the form of a PostgreSQL table (let's assume it's in your public schema and called 'postcodes') with columns: postcode, easting, northing...
You could firstly add an empty geometry column:
ALTER TABLE public.postcodes
ADD COLUMN geom geometry(POINT,27700);
...
Top 50 recent answers are included
