Hot answers tagged

39

Mapperz's answer is invalid. Sinus must be calculated from latitude and NOT from longitude. So corect SQL statement is: SELECT id, ( 3959 * acos ( cos ( radians(78.3232) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(65.3234) ) + sin ( radians(78.3232) ) * sin( radians( lat ) ) ) ) AS distance FROM ...


36

The SQL statement that will find the closest 20 locations that are within a radius of 30 miles to the 78.3232, 65.3234 coordinate. It calculates the distance based on the latitude/longitude of that row and the target latitude/longitude, and then asks for only rows where the distance value is less than 30 miles, orders the whole query by distance, and limits ...


21

Ok, this is hilarriuusss. I tracked this down. In an old copy of lwgeom/lwgeom_spheroid.c in PostGIS 1.0.0rc4 you can see this, /* * This algorithm was taken from the geo_distance function of the * earthdistance package contributed by Bruno Wolff III. * It was altered to accept GEOMETRY objects and return results in * meters. */ PG_FUNCTION_INFO_V1(...


13

$sql = "SELECT points.name FROM polygons, points WHERE ST_CONTAINS(polygons.geom, Point(points.longitude, points.latitude)) AND polygons.name = 'California'";


11

If all you need is the tables of IDs, text, numbers (no geometries), then your best option is to use ODBC. You can install a MySQL ODBC driver for your system: http://www.mysql.com/downloads/connector/odbc/ download "Windows (x86, 32-bit), MSI Installer". (ArcGIS is still a 32-bit program, even on a 64-bit computer, so you will always require a 32-bit driver ...


8

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


7

Based on your descriptions, you don't need a GIS as much as you need the data. You said it yourself: You'd have to track down administrative border data and census data. (If you let us know which area of the world you need, we might be able to offer guidance.) The GIS functionality mentioned so far is limited to simple "Identify" operations in a web map. I ...


7

If you are using MySQL 5.6.1+, take a look at ST_Contains. Given table called points and polygons, with a primary key called id and a geometry column called geometry, this should work: SELECT points.id FROM polygons, points WHERE ST_CONTAINS(polygons.geom, points.geom); If there's no geometry column for the points table, but there are latitude and ...


6

Your best bet is to use OGR directly from the command line. OGR's MySQL Driver page has an example of how to use ogr2ogr to import a shapefile: www.gdal.org/drv_mysql.html


6

SELECT s.name, s.type, ST_Distance(s.geom, p.geom) As distance, s.geom FROM shops s, people p WHERE p.name = 'tom' AND s.type = 'butcher' AND ST_Intersects(s.geom, ST_Buffer(p.geom, 500)) ORDER BY distance; One note, this assumes that both layers are in the same projection, and that projection can't just be lat/long or you'll need to use ST_Transform ...


6

The only plugins I know that involve databases in QGIS are: DB Manager SQL Anywhere plugin eVis You can also import MySQL layers directly via: Layer > Add Vector Layer... > select Database and your type.


5

MySQL should be following the WKT specification that was detailed by the Open Geospatial Consortium's Simple Feature Access - Part 1: Common Architecture. The text you have is not valid WKT, and no GIS software will accept it (generally it will raise a parse error). Commas are used to separate coordinates and spaces between components of each coordinate. ...


5

You could use TileMill to make tiles as MBTiles: https://www.mapbox.com/tilemill/ Then use something like python-mbtiles to serve it up: https://github.com/perrygeo/python-mbtiles.


5

You cannot legally cache or store results from Google's Map API (with pretty narrow exceptions). From the Terms of Service (with emphasis added): 10.1.3 Restrictions against Data Export or Copying. ... (b) No Pre-Fetching, Caching, or Storage of Content. You must not pre-fetch, cache, or store any Content, except that you may store: (i) ...


5

The actual answer is a mixture of the other answers/comments you've already received. MySQL is limited I see from the tags you're using MySQL, the problem is, it can only do bounding boxes until 5.6, not the actual geometries. http://dev.mysql.com/doc/refman/5.0/en/spatial-relation-functions.html If you want to use the bounding box: SELECT * FROM `test` ...


4

I had the same issue. Try this, worked for me: $geojson = array( 'type' => 'FeatureCollection', 'features' => array() ); and change the array_push to: array_push($geojson['features'], $feature); Cheers Henrik


4

Your assumption is pretty much correct. MySQL's spatial support is ... simplistic to say the least. It does work, but you'll find very few GIS applications support it. This isn't specific to just polygons, any spatial feature is more limited in MySQL because while it does store them in the same way (WKB), there simply aren't as many functions to manipulate ...


4

The first question is to decide why you want to move to a noSql database. Moving just because its the new way of doing things may not be your best option. The first thing to decide is what a noSql database gives you that traditional Sql can't. I would suggest that for a vehicle tracking system the answer is not a lot. I would probably stick with Sql but ...


4

From QGIS 2 advanced changelog you can read : Feature: Save styles in your database If you are using a database vector data store, you can now store the layer style definitions directly in the database. This makes it easy to share styled layers in an enterprise or multi-user environment. (source : http://changelog.linfiniti.com/version/1/) It works for ...


4

It doesn't look like it is included in the most recent GDAL-Complete framework. Please file a request with kyngchaos.com. In the meantime, if you don't mind trying it yourself, you can attempt to build a shared plugin for GDAL 1.10, install it, and see if it works with your data source: Ensure you have XCode and/or its command line tools installed. ...


4

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


4

You can use Opensourse JavaScript libraries like leaflet or Openlayers to create a web appilication using the maps created using QGIS with the help of GeoServer and load it into a webview in your android application. If you have only static data then you can use qgis2leaf plugin in QGIS to create a web map and load it to a webview


4

OK, so the main struggle (and obvious error) is the newbie mistake of not controlling which code runs in PHP in the server, which code runs in JS in the browser, and not having control over which variable holds what. Do read: «How to pass variables and data from PHP to JavaScript?» Seriously, read that. If you copy-paste my code, the stackoverflow gods ...


4

This is fixed in Mapserver-7.0.3 and backported to other versions (6.0.5, 6.2.3, 6.4.4). Looks like Kain's question was taken as a bug report which was subsequently fixed. See OGR error messages too verbose .


4

While the geohash representation is convenient, it is not designed with spatial queries in mind. From my understanding of the Geohash algorithm you can't just select all the points inside a given bounding box by simple string comparison. This image might help understanding it a bit better: (image from http://www.movable-type.co.uk/scripts/geohash.jpg) If ...


3

Quoting from the GDAL documentation for the Esri Shapefile driver: An attempt is made to read the LDID/codepage setting from the .dbf file and use it to translate string fields to UTF-8 on read, and back when writing. LDID "87 / 0x57" is treated as ISO8859_1 which may not be appropriate. The SHAPE_ENCODING configuration option may be used to ...


3

In a nutshell, you need to use a projection library. PostGIS uses one (PROJ.4), but MySQL doesn't. The "ST_Distance_Sphere" like functions are much easier to implement, since the math is simpler (see Great-circle distance formulas, or other good examples). The "ST_Distance_Spheroid" like functions are more accurate (the shape of the earth is closer to a ...


3

Each data provider has its URI and its provider key. Once added via User Interface, it's easy to collect them and use them from the console. Enter this statement in your python console, to collect the URI and key for all map layers: for layer in QgsMapLayerRegistry.instance().mapLayers().values(): uri = layer.dataProvider().dataSourceUri() ...


3

What is the purpose of the application? To give advice on what NoSQL database to use that is necessary information. For instance: 1) Do you need ACID transactions? 2) What is the ratio between R/W? 3) Throughput (how many R/W per sec)? 4) Number of simultaneous users? 5) Environment (Java/Microsoft)? 6) How do you plan to connect to the database? ...


3

ESRI doesn't make a DBMS, they make middleware... But on databases, you should almost certainly use PostGres (PostGIS). This is the operation you want: ST_Contains. I think PHP is the easiest language to use for this, but get the query working in PGAdmin, then do it in your server side scripting language. Or whatever!


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