Hot answers tagged

30

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


29

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


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


15

Mehul, I used to work in the address verification industry with a company called SmartyStreets. There are lots of geocoding services out there, but only few will support batch processing with the volume you require. (Google and others don't permit bulk use of their API or storing/caching results.) If you go to your MySQL database and perform an export of ...


12

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


11

If you like Python, you could use the GeoPy API, combined with the GDAL Python bindings or Fiona, and create a very basic script like this for converting the addresses to a point shapefile. This will geolocate a file named 'addresses_to_geocode', creating an output shapefile named 'my_output.shp' in my_output folder: import os from geopy import geocoders ...


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


6

Try changing your Polygon WKT to this (note the extra parens): POLYGON((50.866753 5.686455, 50.859819 5.708942, 50.851475 5.722675, 50.841611 5.720615, 50.834023 5.708427, 50.840744 5.689373, 50.858735 5.673923, 50.866753 5.686455)) That's off the cuff and I haven't tried it yet, but well-formed WKT for Polygons has to support both the outer and inner ...


6

That whole folder is an ARC/INFO coverage. You actually need to keep the entire tmin folder intact, including the info subfolder. You're only going to be able to read these with ESRI software. You should instead download worldclim.org's generic grid file format (.bil). You can probably use GDAL or GRASS to convert it to ascii, and then to MySQL.


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

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


5

You need the tables addr, addrfn, and featnames. And the geography in the edges layers. addr These are address ranges, just address ranges. They relate to feature (i.e. road) names via... addrfn A table relating address ranges and feature names. addr.ARID -> addrfn -> featnames.LINEARID featnames A table of feature names. Each edge (line) can have ...


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


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

I haven't been coding in PHP lately but I think you're just not layering your data structures properly. You are pushing the $feature onto $geoson every time. Try making 'features' an array like so: $features = array(); $geojson = array( 'type' => 'FeatureCollection', 'features' => $features ); Then push each $feature in your loop ...


4

I'm using MySQL spatial tables everyday with QGIS in r&w mode using Add Vector > Database > Type: MySQL and defining a new connection, just like described in http://getspatial.com/gisblog/qgis-vector-data-connection. I can connect both to local and remote server. I'm not using the original geometry definition and not converting it to WKT. Do you have ...


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


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

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

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


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