Tag Info

New answers tagged

2

There is ST_LineMerge function http://postgis.net/docs/manual-2.0/ST_LineMerge.html You could try to serve all your rivers network as one MultiLineString ST_LineMerge(ST_Multi(St_Collect(geometry))) The result is also a MultiLineString with segments sewed together. So after ST_LineMerge you could get sewed segments via ST_Dump.


0

Not tested, but it might give you what you want. With intersections you get those ids and geometries that intersect with others. And the SELECT statement will ST_Union all of these records that intersect exactly with two other geometries. ST_Dump makes sure you don't get MultiLinestrings as a result. WITH intersections AS ( SELECT l1.id, ...


2

You're almost there, you need to declare the sequence as a dependent of the table. ALTER SEQUENCE polygon2_gid_seq OWNED BY polygon2.gid; Now the sequence will cascade drop with the table.


2

There are a couple of questions about equality in this site What is the PostGIS 2.x equivalent of the pre-PostGIS 1.5 ~= operator? ST_Equals Postgis problems The geometries of your example are not "spatially equals", not share the same space, so st_equals will return always false. What output do you expect? Code used to generate the image: from ...


3

Instead of a trigger, who can add a constraint based on st_isvalid(), like: ALTER TABLE public.uniteobservation ADD CONSTRAINT enforce_valid_geom CHECK (st_isvalid(geom));


2

Yes, if you want ST_Area() on a bare geometry to return a planar area, you need to use an area-preserving CRS. GEOS is not magic, it just works in whatever units are handed to it, assuming cartesian math, so the expectation in a planar CRS if you want useful areas/lengths etc.


2

Here is my idea: Use ST_AsRaster to rasterize your vector layer (think about the resolution of the new raster layer) Then use the ST_MapAlgebra function to create a new raster (new_raster values have raster values where vector_raster values are NULL or something like this.)


0

The topology extension has to be CREATEd separately: CREATE EXTENSION postgis_topology; As to why also your raster functions fail: could you verify you are trying to use a version of PostGIS with raster support compiled in? (see http://www.postgis.net/docs/RT_FAQ.html#idp61635392)


0

I would approach this by creating a single table, or possibly a few tables if the separate files have some sort of obvious grouping, with 3 columns, a geometry column, a file column containing the filename, and an attributes column that contains the attributes associated with each feature. Postgres has several datatypes that can store multiple values ...


0

from the NEWS files in postgis repository, it seems that with postgis 2.2.0, SFCGAL can now be installed as its own extension using CREATE EXTENSION postgis_sfcgal so this will be easier in the future ! :)


1

You get this error when there are no results, so the query returns 0 records, and row[0] does not exist. Using another postgres query method you can avoid this error: $result = pg_exec($dbconn, $query); $numrows = pg_numrows($result); // Loop through rows in the result set for($i = 0; $i < $numrows; $i++) { $row = pg_fetch_array($result, $i); ...


2

You'll have to download the EPSG database and pull the information you want from it. They do provide PostgreSQL-compatible downloads. http://www.epsg.org/DownloadDataset


0

try to use collector for ArcGIS with cartoview www.cartologic.com/cartoview . With cartoview you can define a web map and serve it out with a webmapID, authentication and communication to cartoview follows the same REST standards like ArcGIS portal, you can use web appbuilder and other templates from ESRI together with CartoView, it is opensource


1

You could use the &&& operator. It returns true if two n-d bounding boxes intersect: SELECT 'POINT Z (0.5 0.5 0.5)' &&& 'LINESTRING Z(0 0 0, 1 1 1)' AS intersects; intersects ------------ t (1 row) SELECT 'POINT Z (1.5 0.5 0.5)' &&& 'LINESTRING Z(0 0 0, 1 1 1)' AS intersects; intersects ------------ f (1 row) For ...


1

Don't be too alarmed by the GeoJSON producing SQL -- I know how cross-eyed it can make one. Ultimately, you have a table jbb2013 in the final FROM clause, and you want to limit that to those points that it intersects based on a customer_id, so you can do a standard spatial join, as a sub-query where you select only those points that match the particular ...


1

SELECT polygons.* FROM polygons, points WHERE ST_Contains(polygons.wkb_geometry, points.wkb_geometry AND points.id IN (SELECT your condition); Is that what you are looking for?


2

In your case I would recommend to set up a WMS service using mapserver or something equivalent, and then requesting tiles from the WMS endpoint. http://mapserver.org/ogc/wms_server.html This is a very flexible approach, as on the backend you do not need to worry about the exact TMS tiles definitions (indices and bboxes etc). Instead, let the frontend ...


2

You have two issues mixed. First: pgRoute, uses precreated topology based on LineString geometry. Such geometry doesn't have direct links on osm Nodes. You could get geospatial points for built route, not osm Nodes. To get Nodes, you could build an index among osm Nodes geometries and perform distance lookup. In other words, select all nodes which are not ...


1

You need to also grant privileges for the table's sequence too: GRANT USAGE, SELECT ON SEQUENCE topology_id_seq TO public; Or if you want to provide liberal privileges for all sequences: GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO public;


1

You need to use osm2psql for example, if you want to import data, that is not (road) network data. osm2pgrouting only handles linestring geometries and will not work with point data.


3

Postgis is an extension to Postgres, rather than a stand alone application, that provides a spatial data type to Postgres, and provides numerous spatial functions that operate on geometry(ies). Spatial indexing, which you will surely need to find n closest points efficiently, is implemented as an extended R-tree, but the indexing mechanism comes from ...


3

SRID:102631 is an Esri well-known ID given to NAD 1983 State Plane Alaska Zone 1 (US survey feet). This definition isn't in the EPSG Geodetic Parameter Registry. You might be able to munge the prj file's WKT into something that PostGIS will support. One way would be to find an existing WKT/definition for the same zone but using meters for the unit of ...


1

as you can see in your relation_members table, the member_role belonging to your id ( 57582 ) is 'R' <<-- Relation. So your id (108786) contains another relation. Check: select * from relation_members where id = 57582; to see if there is another relation nested within this one. Your can select the id from the nodes or way table if the corresponding ...


1

Grant the following privileges on the appropriate database/schema to your user, you should then, be able to read the data into Cadcorp SIS 7.1 / 8.0. GRANT select ON ALL TABLES IN SCHEMA public TO username;


1

Your Reader and Writer types are incorrect. They should be readers.las and writers.pgpointcloud, respectively. The name change happened here: https://github.com/PDAL/PDAL/commit/b7cde7e1fb13f28db1de7797dbb31392db1f7dfc. You can run pdal --drivers to get a complete list of drivers supported on your system.


0

In pgrouting, pgr_trsp - Turn Restriction Shortest Path (TRSP) does exactly what you are looking for. Instead of specifying source and target nodes, you can specify source and target edges, and the fraction along the edge where your origin and destination are located. (You can use ST_Line_Locate_Point to get that fraction from your point geometry, assuming ...


2

Yes, it's a projection issue. OpenLayers defaults to geographic coordinates (lat/lon) so a simple "show me the thing" with it will paint it in geographics. GeoJSON.io wants to overlay things on top of web-mercator maps, so it reprojects your data into mercator, which stretches things out at the poles, just as you are seeing.


1

Did you try to check if this id (57582) is present in your table 'relations' ? I'm not really used to the pgnapshot schema but i guess it should be referenced itself as a relationship in your database ? Hope you will find (or already have) a solution to your answer!


1

You can use the following query to union the geometry, find all nodes and split the lines: SELECT row_number() OVER() new_id, geom FROM (SELECT (ST_Dump(ST_Node(ST_Union(the_geom)))).geom geom FROM multilines) a Then you must play a little bit to retrieve the original attributes by joining the previous table on "contains" or "within".


0

The solution arrived in PostGIS 2.0, ST_IsValid() and ST_MakeValid(). You can ask PostGIS to fix broken geometries. You must be careful with it tough it the source geometry's integrity is important for some reason.


2

Use the GeoServer SQL view feature, and SQL's ST_Project like, SELECT id, ST_SetSRID(ST_MakeLine( ST_MakePoint(lon,lat), ST_Project( ST_SetSRID(ST_MakePoint(lon,lat),4326)::geography, distance, pi()*azimuth/180.0)::geometry ),4326) AS geom FROM mytable; If you have long lines and want to plot them as great circles on a ...


1

You could use the MAX aggregate function on a subquery that does the spatial query. E.g. SELECT aid, bid, MAX(noOfThings) FROM ( SELECT a.id AS aid, b.id AS bid, b.noOfThings AS noOfThings FROM table_A AS a, table_B AS b WHERE ST_Within(b.polygon, a.polygon) ) GROUP BY (aid, bid); (untested) I've not called the column number, because it's a ...


1

I am not sure but minumum bounding rectangle function may still be missing from PostGIS http://lists.osgeo.org/pipermail/postgis-users/2012-June/034419.html Meanwhile you can use OpenJUMP which can read and write data from PostGIS.


0

The flat nodes file is used directly by osm2pgsql, not by PostgreSQL. To change the location, you just have to stop automatic updates, move the flat nodes file, and change the osm2pgsql command line being called to use the new location. If you are not updating your database, you do not need the flat nodes file at all, as it is only used by osm2pgsql.


1

If you want to extract the few addr:* tags attached to streets, the current version might help. But you'll need Java to overwrite some standard behavior. Implement your own WayTagResolver or extend the Default one. There is a "meta"-attibute which can be populated withr custom data. Nevertheless, GeoCoding is one of the next steps I'm going to implement. One ...


1

I followed this guide by a Duncan Golicher: https://duncanjg.wordpress.com/2012/11/20/the-basics-of-postgis-raster/ I was using tiles with an automatic setting originally, but I reset the tiling to 100x100 cells per row and then included the pyramids as shown in the guide like so: raster2pgsql -s 3161 -d -C -I -M -l 4 D:\PostGIS_data\dem.img -t 100x100 ...


0

I think it's your gid Do SELECT seq, id1 AS node, id2 AS edge, cost FROM pgr_dijkstra('SELECT gid As id, source,target, ST_Length(geom2d) as cost FROM public.canada_rail_net', 1, 200, false, false);


0

As per the guides, the best test for determining whether your installation is correct is going to be via CREATE EXTENSION PostGIS; run from psql or PgAdmin.


0

If you want to actually move one raster so that the cells of both rasters align (i.e., 36 cells from the first raster fit perfectly over one cell of the second), then you can register one raster to perfectly overly the other. This overwrites that raster's georeferencing data though. You can also resample one raster with the environment settings such that the ...


0

If you're using PostGIS 2.x, you might like to know that ST_Distance will return the ellipsoidal distance for geography types. But if you have an unprojected geometry type (such as SRID 4326), then you are subject to the distortion that entails. You can visualise the distortion quite easily by viewing the Earth as plate carrée - towards the poles, you'll ...


1

I found an answer with a count & group by: select c.gid, c.*, count(c.gid) from c1 join c2 on st_disjoint(c1.geom, c2.geom) WHERE c2.nom_comm ILIKE 'blabla' GROUP BY c.gid having count(c.gid) >22 ; 22 means disjoint with all features of c2.


0

You can adapt the function in the linked question. Divide the problem into two parts: Estimate the point spacing Create the points For step 1, divide ST_Area(geom) by the required number of points to get the area per point (I'm assuming n is large), then take the square root to get the separation. So, assuming you've chosen one implementation of ...


1

CREATE TABLE test_points as SELECT ST_Intersection(a.geom, b.geom), Count(Distinct a.gid) FROM roads as a, roads as b WHERE ST_Touches(a.geom, b.geom) AND a.gid < b.gid /* !!! Changed "!=" for "<" */ GROUP BY ST_Intersection(a.geom, b.geom) ; If line A (id 1) ...


1

Like Mikkel said, you do not need any kind of advanced graphics card. Tools that run on dedicated servers rarely even use them at all. Instead I would suggest investing in an SSD. For ~350$ (price of the graphics card) you can get a great one. This will make accessing the imagery very fast and is probably be the biggest performance impact you can have.


2

Graphic cards of significant size are generally not required for 2-dimensional GIS. Once you start visualizing 3D-stuff it becomes useful, but that doesn't appear to be your use. The non-requirement of the graphic card is further underlined when establishing a back-office setup like yours. While I am by no means an expert on Mapbender3, I don't think that ...


3

If you are using PostGIS 2.1 or later, ST_Segmentize for the geography type was introduced: ST_Segmentize(geography geog, float max_segment_length) Where geog is a geography object, similar to a geometry with SRID=4326, and max_segment_length is a distance in metres. If you don't want to use geography types, you can cast between like this: SELECT ...


0

I'm going to post this answer but hopefully another contributor will help break down the following, which I think will paint a more coherent picture: What is the impact of the number of states loaded on geocoding? I've got all 50 and I'm seeing a much lower performance than @LR1234567 (i.e., 8x time per geocode). What's the most efficient method for ...


2

Nearest neighbor is exactly what you describe by (1). (2) would be some form of interpolation, bilinear if you just multiply the color values by the percentage overlaps. Docs are here: http://postgis.net/docs/RT_ST_SnapToGrid.html Also consider ST_Resample: http://postgis.net/docs/RT_ST_Resample.html I noticed that the docs don't really describe how the ...


2

In order to do this, your table must contain two coordinates, one for each end of the connection. If you have this, you can do a join on the coordinates to match up endpoints and assign them matching ids. For example, imagine you have a table with the following definition: create table foo (from_x float, from_y float, to_x float, to_y float); Then add ...


2

I guess your problem is the precision of coordinates. using "=" operator only compares bounding boxes and they are stored as float4 while the coordinates is float8. When you extract, I guess you mean using ST_AsText, then you will likely loose some precision. Translating a floating point value to 10 based representation is not always possible. So try to ...



Top 50 recent answers are included