Tag Info

Hot answers tagged

18

I am a great PostGIS fan and have no experience with MySQL so I mght be biased. But from what you write I think of two reasons to switch. first, it will most surely be much easier to implement new features like the season map you mentioned. second, when you today do your trigonometry calculations I guess you are doing it outside the db. if you do all ...


16

If only because you will have a lot more choice in third-party applications for generating maps of your information (mapserver, geoserver, etc, etc) loading data (ogr2ogr, fme, etc) PostGIS would make a better choice. MySQL will only suit if your needs continue to be relatively limited.


11

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


10

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


9

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


9

PostgreSQL with postgis extension is probably the best choice since it's very mature and natively supported by server technologies (like mapserver) and open source desktop GIS like QGIS. I would give you the advice to convert your .tab files to postgis and, at least for that project, to switch from mapinfo to QGIS. Edit : And use ogr2ogr to transform your ...


8

Without a doubt, go for Postgres. PostGres+PostGIS is an fully featured spatial database, and has a lot of documentation, and you'll easily find help from people on forums and here. MySQL was late to the spatial field, and lacks many features which are there in Postgre+PostGIS. Even the community using MySQL for spatial purposes is minuscule when compared ...


7

MySQL has also a spatial extension but, as far as I know (I have never used it), is not as feature rich and stable as PostGIS. If you are considering to use a spatial database, PostGIS is a good choice and the effort of switching will be worth. While MySQL already provides some functionality to store and operate on geospatial data, the functionality ...


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

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': import os from geopy import geocoders from osgeo import ...


6

The MySQL vs Postgis battle rises once again: http://ambergis.wordpress.com/2008/02/19/mysql-vs-postgis/ Note the comment-ers most are from here (gis stack exchange.) links too http://www.spatiallyadjusted.com/2008/02/05/bringing-open-source-gis-into-an-esri-shop/#comment-32680 Have had more successful deployments with postgis than mysql. (depend on ...


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

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


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: http://www.gdal.org/ogr/drv_mysql.html


6

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


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

Well here's a few: Proper Databases (Cheap / free but of variable quality) - you will probably have to convert to mySQL yourself as this is not a standard geospatial format given its relatively poor geo extensions. US restaurants list Factual Places Cloudmade OpenStreetMap APIs (usage restrictions, generally free to a certain level of use) Foursquare ...


5

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


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


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

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


4

There are similar questions: GIS data for fast food industry POI Datasets for North America Seeking shapefiles of business locations that contain answers that may guide you to find the Points of Interest data you are looking for. Particularly take a look at OpenStreetMap, SimpleGEO, and POI Factory. Additionally, you should browse the questions tagged ...


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


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

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


4

MS SQL Server includes spatial functions, but the functions are usually named differently, for example PostGIS's intersect function is named ST_Intersect and in SQL Server it is called STIntersection. Your best bet to migrate would be to use ogr2ogr to move your data from Postgres to MS-SQL, then you'll have to re-write any queries you're using in Postgres. ...


4

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


4

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


4

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.



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