Tag Info

New answers tagged

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

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; -Create ring view r2: CREATE OR REPLACE VIEW r2 as SELECT ff.fid, ...


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 a.gid, b.gid, a.geom <-> b.geom distance, ...


0

Although using the Soil Data Viewer is great. I was able to figure out how to accomplish what I originally set out to do. This document was a good resource. Extracting Soil Orders From STATSGO Instead of getting soil orders I wanted hydrologic groups. I used some python to combine all of the comp.txt files for the state of pennsylvania. I then brought this ...


0

The psql option for executing sql commands is -c and not -e. The -e option means (from the psql help): -e, --echo-queries echo commands sent to server Because the -e option does not take arguments, your sql command is simply ignored. What you need is: -c, --command=COMMAND run only single command (SQL or internal) and exit Also,this ...


0

You need the mapcatalog to store spatial data in the database from MapInfo Pro and for MapInfo Pro to be able to read the spatial data from the database. If you don't have the mapcatalog and your table is spatial/mappable in MapInfo Pro chances are that you aren't reading/writing the spatial data to the database but just storing it in a local .map file ...


0

Connected to my database world_borders, I did (the first and the last point must be equal): CREATE TABLE geometries (name varchar, geom geometry); INSERT INTO geometries VALUES ('Polygon', 'POLYGON((50.488453280451964 30.816523649043251, 50.227886556627098 31.15709614260982, 50.227886335531331 31.157096406770776, 50.227468173402542 31.157506958998855, ...


0

Take a look at the answer to this question. It's not a tutorial, but it might be enough in your case: http://gis.stackexchange.com/a/116444/800


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 ...


0

just change the database name in the following location Nominatim/settings/ Edit the database name in this file settings.php. @define('CONST_Database_DSN', 'pgsql://@/nominatim'); change with your database name @define('CONST_Database_DSN', 'pgsql://@/nominatimMorocco'); If you change your database name here than you also need to change following ...


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 ...


0

WITH sub_count AS ( SELECT count(*) as total_cust from customers ) SELECT a.id AS store, count(b.*), count(b.*)::numeric/sub_count.total_cust AS customer_count FROM stores a, customers b, sub_count WHERE ST_DWithin(a.the_geom::geography,b.the_geom::geography,5000) GROUP BY a.id, sub_count.total_cust


0

Sorry I know my response is a little late. I faced a similar problem inserting Point coordinates to postgis but was successful when I replaced the comma in the coordinates with a space; [-48.23456,20.12345] to [-48.23456 20.12345] INSERT INTO layer_radar (id, geom) VALUES ( '3bf24920-225b-11e4-8c21-0800200c9a66', ...


0

Well PostgreSQL 9.2.2 with PostGIS 2.0.x is the highest version supported officially in MapInfo v12.5, and you are using newer versions! MapInfo v12.5 - Install guide


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

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: ...


0

A little help: Create buffer views: b1 (10000), b2 (50000), b3(100000) example: CREATE OR REPLACE VIEW b1 as SELECT ST_Buffer(the_geom,10000) as geom, store_name, fid FROM stores; Create ring views: r2, r3 example: CREATE OR REPLACE VIEW r2 as SELECT ff.fid, ff.store_name, ST_Difference(ff.the_geom, g.the_geom) as new_geom FROM b2 ff JOIN b1 g ON ...


0

Here is a pseudo SQL. Unfortunately SQL fiddle doesn't support PostGIS. create table customer ( loc real, name text ); create index on customer(loc); create table location ( loc real, name text ); create index on location(loc); insert into customer values (1,'c1'), (2,'c2'); insert into location values (1.1, 'l1'), (1.2, 'l2'); select cat, count(*) ...


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 ...


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

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

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.


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" ...


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 ...


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


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;


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: ...


0

My current workaround is to build a side table with 2 columns, the objectid and geometry( as PostGIS geometry). And to maintain that table with triggers on the featureclass. Then perform spatial queries against the side table and join back to the featureclass. I have an open incident with Esri, but so far, no luck. I will update this answer if/when we make ...


0

Use mukey to link the soils, not musym(the soil's name). If you are having continued problems, contact the specific state's NRCS State Soil Scientist for the assistance. They will have someone on staff that can help with more specific questions. When we make Hydrological group soil maps, we don't use the break downs from the Web Soil Survey(WSS). We ...


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, ...


0

CartoDB doesn't support direct connections to user databases, so you'd need to use our SQL API in order to connect both: http://docs.cartodb.com/cartodb-platform/sql-api.html


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 ...


1

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


0

You can also combine rgdal and RPostreSQL. This example function creates a temporary table with RPostgreSQL and sends it to readOGR for output of a spatial object. This is really inefficient and ugly, but it works quite well. Note that the query has to be a SELECT query and the user needs to have write access to the database. RPostGIS <- ...


2

I think that you have to configure postGIS library as extension in your database. Here an example.


3

ST_Collect is probably not the function you are looking for, as this simply combines geometries into a geometry collection of some type, and does not actually union/dissolve them. ST_Union, on the other hand, does dissolve overlaps, and assuming polygonal input (which is most probable in conjunction with ST_Buffer and an input table called point), and ...


1

osm2pgsql is surely designed to import the whole planet into postgis. You have to make sure to use a recent version of osm2pgsql, because the node numbers in OSM have hit a limit which made it necessary to change the number format from integer to long integer. Older versions of osm2pgsql will fail therefore. On windows, there are a few more pitfalls. You ...


0

Maybe osmosis is the option you are looking for. You can use osmosis to import all tags into the postgis database. Since osmosis does not build GIS ready to use objects you must do this in a separate process using SQL. A very good collection to start with are the osmosis layers from moenk available on github.


1

You could add row numbers to the points and then join points and non-spatial table based on row number and ogc_fid (assuming ogc_fid starts with 1).


1

Currently there is a method to filter geometries within some distance in PostGIS that uses spatial index: http://postgis.net/docs/ST_DWithin.html


2

This is a case sensitivity/quoting issue. "PointsForGpxExport" and PointsForGpxExport are not the same table name. PostgreSQL, per the SQL standard, case-folds unquoted identifiers. (It case-folds to lowercase, where the standard says uppercase, though). So when you write PointsForGpxExport, PostgreSQL treats that as the same as pointsforgpxexport. Since ...


1

The main thing is getting a correct connection string, so you're doing the right thing playing w/ ogr_fdw_info until you get a connection working. "MSSQL:server=localhost\SQL2012Express;database=ProSpatial;trusted_connection=yes;" Here's an OGR connection string I found online, and there's another on here at GIS.SE, Shapefile to MSSQL with ogr2ogr fails ...


0

It seems that it is not possible to restrict Layer->Add Layer->Add PostGIS layers to a subset of the schemas (schemae? TNIRN) except if that subset is schema = public. The restriction to only load tables in the public schema, is in the connection information ("only look in the public schema" checkbox). The solution I have hit on is to copy the table/s ...



Top 50 recent answers are included