Tag Info

Hot answers tagged

4

There is simple query for this case. SELECT a.id AS store,count(b.*) AS customer_count FROM stores a, customers b WHERE ST_DWithin(a.geom::geography,b.geom::geography,5000) GROUP BY a.id I have stored my sample data in the projection WGS 84 (4326). When you want to use a metric system, you have to convert the geometries to geography format. The ...


4

ST_Polygonize will do the job: CREATE VIEW boundarypolygons AS SELECT g.path[1] as gid, g.geom::geometry(polygon, 31492) as geom FROM (SELECT (ST_Dump(ST_Polygonize(geom))).* FROM boundary ) as g;


4

You can go with the SQLExecuter, the initiator would need to be a list of database values [keys] you want to delete. Then using something similar the SQL statement below to execute the delete query should work. DELETE FROM table WHERE key = '@Value(key)'; Or you can use the DatabaseDeleter


2

Might want to try HausdorffDistance. Suited for this kind of question. SELECT ST_HausdorffDistance('0101000020110F0000FE546AD2413E5EC16D56617C93C74FC1'::geometry, '0101000020110F00009D7E6384413E5EC10185DD7F93C74FC1'::geometry); Detailed here: http://postgis.net/docs/ST_HausdorffDistance.html And as explained at /wiki/Hausdorff_distance: Informally, ...


2

What you are looking for is essentially an upsert (albeit without the update part). This is not part of ogr2ogr and is fairly complicated to implement in Postgres, see the docs, for more information than you would ever want to know about upserts. A simple alternative, would be to use a temp table to insert into from ogr2ogr and then run an insert for only ...


2

Yes, You can open and edit non geometry table from PostGIS in QGIS. You can open PostGIS views in QGIS. Also spatial views. You can edit PostGIS views in QGIS, if you write an 'instead of trigger' in PostGIS.


2

The question is a duplicate of the one here but it may not be so obvious so let me explain. ST_Within is defined as: -- Inlines index magic CREATE OR REPLACE FUNCTION ST_Within(geom1 geometry, geom2 geometry) RETURNS boolean AS 'SELECT $1 && $2 AND _ST_Contains($2,$1)' LANGUAGE 'sql' IMMUTABLE; As you can see the function does use ...


2

Shouldn't ST_GeometryFromText(Point(53.34972 -6.26025)), 4326) have quotes around the WKT since the function expects a text argument? So this instead - ST_GeometryFromText('Point(53.34972 -6.26025)', 4326) which also removes the extra right parentheses that you had after the WKT.


2

Go with steps: -Create spatial indexes for your tables. -Create Buffers CREATE OR REPLACE VIEW b1 as SELECT ST_Buffer(the_geom,500) as the_geom, attributes, fid FROM stores; CREATE OR REPLACE VIEW b2 as SELECT ST_Buffer(the_geom,2000) as the_geom, attributes, fid FROM stores; (I am not sure, but maybe you should reduce here you 8 million point to ...


2

Use UNION or UNION ALL to build one result from your two selects: insert into table_1 (name, id) select name, id from table_2 limit 1 UNION select name, id from table_3 limit 1; UNION will remove the duplicated rows from the union, UNION ALL will include all.


2

You could try using geoalchemy2. Personally I would look at using the Object Relational Mapper (ORM) model for working with your data, for example: from sqlalchemy import create_engine # Enter your database connection below engine = create_engine('postgresql://gis:gis@localhost/gis', echo=True) from sqlalchemy.ext.declarative import declarative_base from ...


2

Create a table or a view from it: CREATE TABLE my_selection AS SELECT name, neighname FROM neighborhoods WHERE ST_Intersects(geom, ST_GeomFromText('LINESTRING(586781.701577724 4504202.15314339,586863.51964484 4504215.9881701)', 26918));` Although if you want it in QGIS you will also want to select the geometry so: CREATE TABLE ...


1

The omission in the dbtune parameters have been acknowledged, by Esri development and are intended to be addressed in the next release. The changes will address the ability to control the placement of the spatial index on the business table, the a table (created by registering the feature class as versioned) and the location of the sde log files.


1

One possible improvement would be to create the line buffer once, and re-use that for each raster. As currently you're recreating the buffer for each raster. WITH linebuffer AS ( SELECT ST_Buffer(ST_Makeline(CAST(startloc AS GEOMETRY), CAST(endloc AS GEOMETRY)), 75) AS linebuffer_geom ) SELECT ST_UNION(rast) INTO elevationdata FROM uk_rasters, linebuffer ...


1

Normally, the shp2pgsql binary is in /usr/bin/shp2pgsql when you install with packages like in the method you mentioned. Did you type shp2pgsql in command line? Normally, it works out of the box. If not, did you do an updatedb (command line to index files) followed with a locate shp2pgsql to find the real binary path? did you echoed the output from the ...


1

This question is more than a little confusing ("point data is sample not really very" what?). Your SQL query syntax is highly irregular, and fails to use the correct function to take advantage of a spatial index on the point layer. What you probably want is: SELECT b.shape FROM table_b b, table_a a WHERE ...


1

Have a look at leaflet maps - gps plugin (http://labs.easyblog.it/maps/leaflet-gps/) It automatically updates the gps location received from the device (html5 geolocation). I suppose you can attach a javascript to that updating event, to look up any POI's within a certain buffer around that gps point. These POI's can then be fed back into the leafletmap ...


1

Your query looks rather strange. What are you trying to achieve with two buffers around the same point? You can replace ST_Distance with operator, but still we have to overthink the buffer thing. Could you specify your goal a little? PS: Did you build spatial index? UPDATE: I would try SELECT * FROM ( SELECT a.gid, b.gid, ...


1

Rather than leaping direct to WPS you might find you can get what you need out of the GeoServer SQL View feature. http://boundlessgeo.com/2015/03/support-story-getting-sql-views/ http://docs.geoserver.org/stable/en/user/data/database/sqlview.html It allows you to write some arbitrary SQL and bind some query parameters into it, so the query becomes a ...


1

There are several tools you can use to achieve a format conversion like this, among them, in no particular order: Open Source Server solution: GeoServer, which has capabilities to import various files and store them in a PostGIS db. Info is available here: http://docs.geoserver.org/2.6.x/en/user/data/vector/shapefile.html Open Source Desktop Solution: ...


1

The Solution Thx to AndreJ to show us the way. First you have to know that when adding a Layer from your database, the database parameters are burnt into the QGIS project file. This mean if you modify those parameters from QGIS source, it will not affect you project file. If you need to modify those parameters there is only 2 solutions : 1/ Remove the ...


1

Thusly: SELECT Max(id) AS id, array_to_string(array_agg(name), ',') AS all_name, geom FROM thetable GROUP BY geom; The only fiddly bit is there's no nice way to get the geometry and id group the grouping together cleanly, so ended up only grouping on geom. If there was a "max(geometry)" aggregate or something it would be easier to group by "id" ...


1

Since you are using PostGIS and QGIS, you can try to convert them using both, to decide which one is best suited for your problem. To use PostGIS, the LINESTRING must be closed. You can check if they are closed with the query: select gid, st_isclosed(geom) from boundary; If the lines are closed, you can create another table to check the results, with: ...


1

The Soil Data Viewer from the NRCS makes querying single attributes from SSURGO data relatively easy.


1

Jan, Not sure when you downloaded that binary. In first packaging I made a mistake with the 9.3 build and left out the ODBC driver which is needed for the MSSQL connection. Someone alerted me when they tried to connect to MS Access and I since fixed the issue. Also if you upgraded PostGIS since, it may have overwritten the libgdal driver and the one that ...



Only top voted, non community-wiki answers of a minimum length are eligible