New answers tagged postgresql
1
Since you only want to download data from healthsites anyway:
Go to https://healthsites.io/map
Download all healthsite data in this World.zip (last update at : ...) --> click on "World.zip" to download that file.
5
Run the command with -d switch https://postgis.net/docs/using_postgis_dbmanagement.html#shp2pgsql_usage
-d
Drops the database table before creating a new table with the data in the Shape file.
for overwriting the table that you have created with your previous trials. The first error message tries to help ERROR: relation "geo_br" already exists.
3
I have experienced similar issues, where data that is perfectly fine according to PostGIS and QGIS (tested with ST_MakeValid in PostGIS, and by loading the data in QGIS and running QGIS's geometry validity checker), still couldn't be loaded reliable in ArcGIS.
Note that the data in my case was stored in WGS 1984 (SRID 4326) "geometry" storage (so ...
0
Another possible solution using ST_Split is given in this PostGIS User list thread.
However, it relies on the lines having no coincident linework (since in that case ST_Split errors out).
1
Here's a solution that makes use of the fact that in PostGIS running ST_Difference on two intersecting LineStrings (or MultiLineStrings) has the effect of noding the first line. So it's possible to loop over every line in the table, and difference it with the collection of lines that intersect it, to effectively node it. The noded line is computed as a ...
0
I got this same error:
ERROR: function addgeometrycolumn(unknown, unknown, unknown, integer, unknown, integer
and turned out it was because I didn't have PostGIS installed. More info here: http://www.silota.com/docs/sql-load-data/shapefile-postgis.html
ogr2ogr to a postgres db only works if you have PostgresSQL and PostGIS installed (and a postgis table ...
1
I realize this is quite old but just came across the same issue and wanted to share a solution. Strictly speaking John Powell is right – it can't be the same format because it needs to be converted on import to a PostGIS-supported form. However, using ogr2ogr you have some control over how this happens:
Something like $ ogr2ogr -f "PostgreSQL" &...
0
I had the very same problem until i disovered I was using 'localhost' as a host reference. Since I am running GeoServer on a Physical Server machine on the network with with physical clients; by entering the server Server's Computer Name or IP address the issue was resolved went through.
3
There are many ways to get the desired result, the main thing is to understand what steps will lead you to the right result!
The main thing is all - the Idea, Method, Process, Result and Emotions!
In general, the Idea is in your fantasy (representation) and is as follows::
We are looking for the most important point that will help us build a inscribed ...
1
Well, it looks like a really complicated question. I don't think that there is an optimum algorithm, but maybe you can find one that fits your particular needs. For exemple, if your polygons can be concave, it will be way more difficult (the blue rectangle would be the answer?):
So you should look at the specificities (only convexe polygon?) of your problem,...
2
So my colleague came up with this solution. It works great for small areas, but I'd like to find a solution for millions of shapes across an entire country. I can see the ST_UNION here causing a blockage in this respect.
The ST_BUFFER is just to close out thin slithers.
SELECT ST_DIFFERENCE(foo.geom, bar.geom)
FROM (SELECT ST_CONVEXHULL(ST_COLLECT(shape::...
1
this code will fill the gaps and holes in the polygons. adjust according to your data
SELECT id, ST_Collect(ST_MakePolygon(geom)) As geom
FROM (
SELECT gid, ST_ExteriorRing((ST_Dump(geom)).geom) As geom
FROM layer
) s
GROUP BY id
2
\d raster_columns describes the table that stores information about rasters, to find out about a specific raster you would query that table, something like:
select * from raster_columns where r_table_name = 'name';
4
Use ST_OrientedEnvelope
Returns a mimimum rotated rectangle enclosing a geometry.
select ST_OrientedEnvelope(geom)
from ok_ak_riks
0
Although, using a combination of GeoServer, PostGIS and QGIS could respond most of your needs you still have a chance to use ArcGIS.
As a architectural point of view, what you can do is to connect ArcGIS software to database containing spatial data and users are working with them. GeoServer has this ability to connect to that database, too.
Consequently, ...
1
You have several hidden questions embedded within this broad one but it is possible to edit data in GeoServer from ArcMap (with a Production Mapping license) via WFS-T.
The ArcMap Help has a page on Data editing with WFS-T services:
Production Mapping provides a way to consume WFS data, perform edit
operations, and publish the resulting changes to the ...
2
You can find below a sample to do it.
import json
import mapnik
mapnik_xml = "openstreetmap-carto/mapnik.xml"
map_output = "region_map_mapnikXml.png"
epsg_3857 = "+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0 +k=1.0 +units=m +nadgrids=@null +no_defs"
bbox_array = [5034980.57, 5062621.68, 5146300.06, ...
2
The unit is degree, as you seem to have geometries referenced in EPSG:4326, and the edge length as cost.
If so, two options
if your cost attribute is the plain length of each edge (shortest path), use a cast to GEOGRAPHY instead, either in a pre-processing step to update the cost and reverse_cost columns, or directly in the edge_query passed to pgr_Dijkstra(...
0
This tutorial provides a very clear overview of how you can import raster data, in this case Sentinel-2 data, into PostGIS using the raster2pgsql tool.
3
The vertex order in
'SRID=4326;POLYGON((0.0 0.0, 0.0 10.0, 10.0 0.0, 10.0 10.0, 0.0 0.0))'
is mixed up; this will result in a self-intersecting (invalid) Polygon, and
[...] in the case of invalid geometries [...] the result is always false regardless or not defined. -docs
It has to be
'SRID=4326;POLYGON((0.0 0.0, 0.0 10.0, 10.0 10.0, 10.0 0.0, 0.0 0.0))'...
4
Among the most versatile approaches would be to use ST_ClusterDBSCAN to create cluster of points based on distance; apart from the general uncertainty of results due to the lack of a common identifier, the only drawback is that it currently only supports the GEOMETRY type and is thus limited to the CRS units (degrees for EPSG:4326).
Running
SELECT ROW_NUMBER(...
1
My understanding of PGRouting is that when you create a network from a line dataset, it will build a 'ways' table, but most importantly build a 'osm_nodes' table from the nodes of the 'ways'.
The actual routing takes place from node to node, and it sounds like here you have way too many nodes in the osm_nodes table.
I would suggest truncating (removing all) ...
0
Store as 4326 as the data spans multiple zones. Convert to a suitable projection to make calculations.
2
If I understand your question correctly, I'll add another solution.
Run the Postgre/PostGIS SQL script:
SELECT ST_X(geom) as lng, ST_Y(geom) as lat FROM (SELECT (ST_DumpPoints(geometry)).geom FROM final) foo
The result: all the points in the polygon in the format you want...
4
To get a table of extracted coordinates from all vertices of the (table of) polygons, run
SELECT poly.<id>,
dmp.path[1] AS ring,
dmp.path[2] AS vtx,
ST_X(dmp.geom) AS lon,
ST_Y(dmp.geom) AS lat
FROM final AS poly,
LATERAL ST_DumpPoints(poly.geometry) AS dmp
ORDER BY
1, 2, 3
;
where
ring [INT] represents the ...
1
I finally resolved the issue. The problem was in two places. The first problem was that I did not create a node network for the roads shapefile in PostgreSQL. Furthermore, I needed to create a network topology specifically after creating the node network for my roads. It would go as:
Create node network for Roads. Result roads_noded table created.
Create ...
6
A polygon does not have a single X coordinate and a single Y coordinate. A polygon is an accumulation of vertices with an X and Y coordinate.
You can use the ST_AsText function to get the WKT representation of your geometry :
SELECT ST_AsText(geom)
FROM polygon -- change the name of your table
-- Result example :
-- POLYGON((-0.606871159358866 44....
2
You can build the SQL query, which should be a string. To do so, use a pre-calculated value and concatenate a string containing the query and the desired variable.
'select 1 + ' || @myval
2
You can store it in EPSG:4326 WGS84 for example. Then cast to Geography from 4326 for distance calculations:
https://postgis.net/workshops/postgis-intro/geography.html
1
To condense all the geometries in a table to one single geometry, you can pass an array to ST_Union:
SELECT ST_Union(ARRAY(SELECT geom FROM tbl))
Top 50 recent answers are included
