New answers tagged

0

Ok, the 1-center problem can be solved using a tree algorithm like Dijkstra. I did not find any good way to reconstruct the complete tree using the much faster Floyd-Warshall algorithm (I think multiple self-joins are necessary). However, the following code solves the problem. Nevertheless, using Dijktra (O(n^2)) we obtain a complexity ~n^3, which is ...


1

John Barca "Change St_Collect(geom) as Geometry to ST_Collect(geom) as geom" Thanks again John. Edit: This seems to make a selection and doesn't actually update the geometry to snap to the points, still closer to what I want.


4

It looks like SELECT ST_AsText (ST_CENTROID(pol8.geom) AS geom should be SELECT ST_AsText (ST_CENTROID(pol8.geom)) AS geom note the second ) - when in doubt count from left to right +1 for each ( and -1 for each ) if you don't reach 0 at the end then you have missed a bracket.


2

Yes, it is a meaningful difference, there will be some difference in reprojecting. Those 7 parameters define a datum shift (translation + rotation + scaling). If the postgis reprojection is better than the other one, just add those parameters to the Proj4js definition. You can find additional information on the towgs84 param here ...


0

You are not adding vectorSource to the map. Are you? Review the OL3 WFS example. Change the url to use your server http://localhost:8080/geoserver/wfs and your layer PFT:PFT_pft_pft. It should look like: var vectorSource = new ol.layer.Vector({ source: new ol.source.Vector({ format: new ol.format.GeoJSON(), url: ...


0

From the header info you show it appears that what you are trying to load is a raster image. GDAL is the correct tool for converting the raster bands from netcdf to for instance geotiff after which you can use raster2pgsql to load the raster into postgis. Basically, you might be done with: gdal_translate mynetcdf.nc mygeotiff.tiff raster2pgsql ...


1

To use different queries for different zoom levels you will have to provide queries for each zoom level that you want to use, I think it should be able to query from a different table as well, since we are only defining the database in the kwargs. I have it working using this layer in the cfg "roads": { "allowed origin":"*", ...


1

I updated everything installed in the server using OSGEO4W installer, which included updating QGIS Server to 2.12.3 and now everything works like a charm! So I guess it must have been a problem in QGIS Server version 2.6.1-2.


4

What you probably mean is how to find polygons that are 'spatially equal'. What you need then is WHERE ST_Equals(geoma, geomb). Read the docs: http://postgis.net/docs/ST_Equals.html


2

The error you're getting is because you're trying to restore a database created with an old version of PostGIS (2.1) to a newer version (2.2). I would recommend trying to downgrade your PostGis version to 2.1.x by using the following command in your database, assuming you have no data loaded in it (Please backup your current DB if you do!) DROP EXTENSION ...


0

Adding to Alexandre's very useful answer, if some of your census units may span three of your towns (and therefore you can't guarantee more than 50% falls in any town) you can do this: select distinct on (b.id) b.*,t.name, (st_area(st_intersection(b.wkb_geometry, t.wkb_geometry))/st_area(b.wkb_geometry)) as proportion from blockgroups b, towns t where ...


3

You can confirm that the discrepancy is a bug by comparing the results of _ST_DistanceUnCached(geog,geog) to _ST_DistanceTree(geog,geog) What is happening under the covers is that the "tree" algorithm only kicks in when a geometry shows up in a query a couple times in a row. At that point the geometry is cached and a tree built on it, for faster distance ...


-1

I solved this problem with Java + Ibatis implementing the interface TypeHandlerCallback. You need to add postgis.jar lib; Create a handle class implement TypeHandlerCallback from Ibatis;


0

I installed postgis-2.2 under Ubuntu 16.04 alpha2 using apt-get on default repositories. And then CREATE EXTENSION postgis_sfcgal; works out of the box. test=# create extension postgis_sfcgal; CREATE EXTENSION test=# select postgis_sfcgal_version(); postgis_sfcgal_version ------------------------ 1.2.2 (1 row)


3

Check the proj definition stored in the spatial_ref_sys, it should be different from the one used in the proj4js definition. For example, mine is the following: +proj=utm +zone=30 +ellps=intl +towgs84=-87,-98,-121,0,0,0,0 +units=m +no_defs Also epsg.io gives the one with the towgs84 parameters, so I'd change the Proj4js def.


2

but this is will not provide me the order and how many times municipalities are crossed. Assuming every row in Rivers table is a linestring with an entire river. Here's a query that will get you how many municipalities are crossed, the subquery t , will get you all the municipalities that each river crosses but yes they will not be in any guaranteed ...


0

I recreated your query to see where it goes wrong. The last error you came across is because of the faulty reference of px instead of geom (partly my mistake because I gave the hint of aliasing with geom) but there would likely have been more errors after that. Here's the query I created out of it, as you can see the sensordata has been replaced with just ...


2

Your original trigger contains an error: CREATE TRIGGER area_calculate **BEFORE** INSERT OR UPDATE ON nutzungen This means that you're triggers calls the function on geom before the geometry is updated. Replace the trigger by calling the following: DROP TRIGGER area_calculate; CREATE TRIGGER area_calculate AFTER INSERT OR UPDATE ON nutzungen FOR EACH ...


2

You have to change 2 things in your query: add an alias 'geom' right after 1, 1, NULL))


2

I'd check the following: first of all, I'd like to be sure that source data are OK, by comparing them with official/cadastral data I'd try to use the qgis openlayers plugin to check if the transformation to 3857/900913 works properly for both 23031 and 25831 Try to reproject using QGIS, just saving the shapefile with the target srid. If using QGIS works ...


2

The ST_ClusterWithin and ST_ClusterIntersecting functions in PostGIS first collect all input geometries into a Postgres array before doing their processing. Since the size of this array is proving to be a limitation, you can process this dataset only if you can reduce the total size of this array. Some options for doing this: Use a GROUP BY clause to ...


2

You must convert point coordinates to geometry SELECT ST_Within(St_GeomFromText('POINT(-73.883573184 40.751662187)'), ST_GeomFromGeoJSON(d));


1

Use DB Manager tool (Database > DB Manager > DB Manager) to connect to your PostGIS database and then add manually the tables / views you want to QGIS map canvas. Before clicking DB Manager, you will need to specify connection details to your DB through Layer > Add Layer > Add PostGIS layers...


0

As MariaDB is a fork of MySQL, I believe it has al the same spatial functionality, which is considerably less than Postgis, but sufficient for your query. You can't GROUP BY a bounding box as such, but you can summarize attributes where the geometries bounding boxes intersect some input geometry. In MySQL/MariaDB, functions that operate on bounding boxes ...


2

Use ST_WorldToRasterCoord(), ST_WorldToRasterCoordX() and/or ST_WorldToRasterCoordY().


0

Based on this new information, I would suggest the following setup to find the street: SELECT point.geom As pointgeom, COALESCE( (SELECT streets.geom FROM streets WHERE ST_DWihtin(points.geom, streets.geom, 30) ORDER BY streets.adt DESC LIMIT 1), (SELECT streets.geom FROM streets WHERE ST_DWihtin(points.geom, streets.geom, 50) ORDER BY streets.adt ...


0

This is a well known issue in osm2pgsql. See https://github.com/openstreetmap/osm2pgsql/issues/16 . This issue is raised when osm2pgsql slim mode is used and multiple geometries are imported. Don't use osm2pgsql slim mode, then the issue will probably go away. edit: Or it might be a stale version of osm2pgsql: ...


0

This page suggests a panel of ways to extract OSM data and display them into Desktop GIS. These are not necessarily based on command lines. It might help you.


0

I just did ignore the messages and went ahead with editing. Everything ok so far. My edits are light though. I guess the problem could be related to the fact that the table was empty. Thank you very much.


0

I've come up with a solution which works: SELECT x.bufid, (ST_SummaryStatsAgg(x.intersectx,1,true)).* FROM (SELECT bufid, ST_Intersection(rast,1,ST_AsRaster(geom, rast),1) as intersectx FROM rasttable, buftable WHERE ST_Intersects(geom, rast)) as x WHERE x.intersectx IS NOT NULL GROUP BY bufid I think the issue was with the ...


3

1500x750 is pretty big for a raster. Definitely worth retiling your data. Take a look at ST_Retile function (new in PostGIS 2.2) - http://postgis.net/docs/manual-2.2/RT_Retile.html or if you are using PostGIS 2.1, you can use ST_Tile instead which achieves same purpose on a per row (instead of per table basis) ...


2

In PostGIS you have so called raster editors to set the transformation parameters. http://postgis.net/docs/manual-2.1/RT_reference.html#Raster_Editors ST_SetUpperLeft, ST_SetScale, ST_SetSkew... And raster accessors to transform the numbers. http://postgis.net/docs/manual-2.1/RT_reference.html#Raster_Accessors ST_WorldToRasterCoord The calculation ...


3

Jibran, it seems that what you are looking for is a nearest neighbour selection. Since it wouldn't matter if the street is 30 or 50 meters away, you just want the closest and then snap to that one, you can do the whole thing from within postgis with the help of just two functionalities: Nearest neighbour selection Point on Line interpolation


2

You were almost there... MultiPolygonZ should do the trick: ALTER TABLE urbana_prueba ALTER COLUMN geom TYPE geometry(MultiPolygonZ, 3857) USING ST_Force_3D(geom);


-2

We have written a mobile geospatial data collection and audit app, that works on any browser enabled device (any device as long as it can enable GPS). It is a mobile app/interface for field data collection and mobile editing of GIS data that relies/syncs back to some type of GIS server or database. We use our own server (MySQL) and your data is stored on ...


1

If I understand correct, your question consists of 2 parts: 1. How to get many linestring to merge to 1 2. How to get the point 1000 km further down the road from point 1 Part 1 needs some more explanation from you. It seems you already did a linemerge and that works for me as well with the data you give. Maybe you can post a new stackexchange question ...


5

The column format for PostGIS is called geometry and you'll probably need to read up on all the functions associated with managing spatial columns. If you are getting the lat/longs from GPS or Google, the projection will be EPSG:4326. edit: iant's comment brings up a good point about the geography type, and indeed if you are using EPSG:4326 it looks like ...


1

There are two issues here: You need to declare your matches variable before you use it, if you are going to use in the manner that you are. You have to get the unqualified names in order to compare them. Otherwise the name of the user or database etc. will make your names not match. I'm also not sure if you have your workspaces written correctly. I ...


2

You need to determine the azimuth of the polyline segment next to the point. It is possible to calculate this value and save it to an attribute, or calculate it as an expression which give the azimuth as result. How to use the following Python script: Point layer and line layer are added to the legend (in the script named bridges and trails) and change ...


0

I think there are some cases where you might justifiably store a point in both geography and geometry type but don't do it until you have to. I would recommend keeping things simple and choosing one or the other unless you run into actual performance issues that only this approach could solve. The drawbacks are It's icky, like storing serialised data in ...


3

You can use the spatial query plugin for this (and many other) query. It will work on either all the features or just the selected ones in both source and target layers. Spatial Query is supplied in the default QGis install but may need to be enabled in your install.


1

It looks like from the documentation that arcpy.FromWKB expects a bytearray data type, which you can get using the bytearray() function: cur.execute(""" SELECT ST_AsBinary(geom) FROM master.modeled_yield_hotspots WHERE model_id = %s; """, (4,)) lsGeom = [] for r in cur: lsGeom.append(arcpy.FromWKB(bytearray(r[0])))


1

To dump only unique points, you could use a few subqueries with a SELECT DISTINCT ON expression: SELECT geom, gid, path FROM ( SELECT DISTINCT ON (geom) geom, path, gid FROM ( SELECT (ST_DumpPoints(the_geom)).geom, (ST_DumpPoints(the_geom)).path, gid FROM my_linestrings_table ) f ORDER BY geom, path, gid ) f ORDER BY gid, path;


0

I am a bit late to the party here. But you can use Grasshopper API - They have a matrices component that you can work within called the Matrix API There is also Mapzen Leaflet Routing Plugin or the Mapzen Valhalla engine as well as Mapbox Distance API which you can tap into the


1

Tidiest approach that I have seen is to use python to trigger the pgsql2shp command line utility which is part of the postgis package. Not very pythonic, but very simple and robust. e.g. See How can I get a shapefile from a postgis query? Once you have a shapefile it is straight forward to convert to most common formats using ogr2ogr, also over the ...


2

If you're already interfacing with your data in PostGIS using ArcPy, you could simply use a SearchCursor, manipulate the data as needed, and then insert it into a shapefile/fGDB feature class using an InsertCursor. You don't need to use Psycopg2 to pull the data out, the SearchCursor can handle that.


5

It stands for "radius". It will only usually be populated for curved geoemtry types.


1

The Hibernate Page page has some fine print that may point to your issues. NOTE: Type parameters (see NHibernate mapping above) are not supported in ActiveRecord. ... NHibernate Mapping: In the XML mappings file, map all properties as usual but for geometry columns use the following entry: <property name="Geometry" column="the_geom" ...


3

No, do not load the imagery into PostGIS. You want to load the image catalogue into PostGIS and use the JDBC image mosaicking support in GeoServer to read the mosaics using the database catalogue. That was you can easily update which images participate in your mosaics via the catalogue in the database, while not incurring the performance penalties of storing ...


0

If your LA boundary data consists of 1 single polygon, it's easy: SELECT a.* FROM tmp_coords AS a INNER JOIN shp_laboundary AS b ON (St_Intersects(a.st_loc, b.geom)) You can safely use intersects, it's faster than within. If you have multiple polygons you first have to merge them with ST_Collect



Top 50 recent answers are included