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.


13

This question was also asked on StackOverlow. The top answer suggests the MySQL Spatial Extensions. There are a load of links on working with these extensions here. If you don't want to use spatial types and you are getting values from a GPS unit, or geocoding service then you can match your decimal precision to the data source. A general rule of thumb is ...


8

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

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


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

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


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

Mehul, I 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 your table ...


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

Unless you are tied to MySQL for some other reason you should really consider using a spatially enabled database like postgis which has a Point (and Line, Polygon etc) object to handle these details for you. You also get projection support for when you make that change to the whole world.


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. Factual Places Cloudmade OpenStreetMap APIs (usage restrictions, generally free to a certain level of use) Foursquare Google Places ...


5

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.


5

Spatial queries are definitely the thing to use. With PostGIS I would first try something simplistic like this and tweak the range as needed: SELECT * FROM table AS a WHERE ST_DWithin (mylocation, a.LatLong, 10000) -- 10km ORDER BY ST_Distance (mylocation, a.LatLong) LIMIT 20 This would compare points (actually their bounding boxes) using the spatial ...


5

With PostGIS 2.0 on PostgreSQL 9.1, you can use the KNN indexed nearest neighbour operator, e.g.: SELECT *, geom <-> ST_MakePoint(-90, 40) AS distance FROM table ORDER BY geom <-> ST_MakePoint(-90, 40) LIMIT 20 OFFSET 0; The above should query within a few milliseconds. For the next multiples of 20, modify to OFFSET 20, OFFSET 40, etc ...


5

If all you are looking for are proximity point searches (nearest neighbour queries), then you don't want to use the old ST_DWithin or ST_Distance + ORDER BYs for that. Not anymore. Now that PostGIS 2.0 shipped, you should be using the knngist index support (a native PostgreSQL feature). It will be orders of magnitude faster. An excerpt from this blog ...


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

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


4

There are similar questions (Q1, Q2, Q3) 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 as POI, business, data, and datasets. Whatever dataset you choose to go with, be sure to read ...


4

Something must be wrong with your mysql installation or the .ini settings. Just tested a geospatial index on my old mac (10.6.8 / MySQL 5.2). That configuration is similar to yours and I tested the big geodata dump (9 million records). I did this query: SET @radius = 30; SET @center = GeomFromText('POINT(51.51359 7.465425)'); SET @r = @radius/69.1; SET ...


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

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

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

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