Tool für Umzug für Mailkonten

Mailstore Home

http://www.mailstore.com/de/mailstore-home.aspx

JSON Datenbank

Postgressql ab Version 9.3
http://www.enterprisedb.com/products-services-training/pgbindownload
http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-json-operators/
CREATE TABLE aa (a int, b json);
CREATE TABLE aa (a int, b varchar(9000));
INSERT INTO aa VALUES (2, '{"f1":{"f11":11,"f12":12},"f2":2}');
INSERT INTO aa VALUES (2, '{"Response":{"MetaInfo":{"Timestamp":"2013-06-14T14:27:51.580+0000"},"View":[{"_type":"SearchResultsViewType","ViewId":0,"Result":[{"Relevance":1.0,"MatchLevel":"houseNumber","MatchQuality":{"City":1.0,"Street":[1.0],"HouseNumber":1.0,"PostalCode":1.0},"MatchType":"pointAddress","Location":{"LocationId":"NT_iFnXKE181rtRfm56s4Pm6B_10","LocationType":"point","DisplayPosition":{"Latitude":50.9767799,"Longitude":10.3006897},"NavigationPosition":[{"Latitude":50.9766884,"Longitude":10.3007097}],"MapView":{"TopLeft":{"Latitude":50.9779041,"Longitude":10.2989043},"BottomRight":{"Latitude":50.9756558,"Longitude":10.3024751}},"Address":{"Label":"Siebenbornstraße 10, 99817 Eisenach, Deutschland","Country":"DEU","State":"Thüringen","County":"Eisenach","City":"Eisenach","Street":"Siebenbornstraße","HouseNumber":"10","PostalCode":"99817","AdditionalData":[{"value":"Deutschland","key":"CountryName"},{"value":"Thüringen","key":"StateName"}]}}}]}]}}');

select (b->'Response'->'View'->0->'Result'->0->'Location'->'DisplayPosition') from aa


insert into aa (a,b) select a,to_json(b) from ab
to_json ist falsch, einfacher cast auf  json
insert into aa (a,b) select a, json(b) from ab

select a,
(b->'Response'->'View'->0->'Result'->0->'Location'->'DisplayPosition'->'Latitude'),
(b->'Response'->'View'->0->'Result'->0->'Location'->'DisplayPosition'->'Longitude')
from aa order by a limit 20
select a,
(b->'Response'->'View'->0->'Result'->0->'Location'->'DisplayPosition'->'Latitude') as Lat,
(b->'Response'->'View'->0->'Result'->0->'Location'->'DisplayPosition'->'Longitude' ) as Lon
from aa
order by a


select a,
(b->'Response'->'View'->0->'Result'->0->'Location'->'DisplayPosition'->'Latitude') as Lat0,
(b->'Response'->'View'->0->'Result'->0->'Location'->'DisplayPosition'->'Longitude' ) as Lon0,
(b->'Response'->'View'->0->'Result'->0->'Relevance' ) as relevance0,
(b->'Response'->'View'->0->'Result'->0->'MatchQuality' ) as mq,
(b->'Response'->'View'->0->'Result'->0->'MatchQuality'->'City' ) as CityMQ0,
(b->'Response'->'View'->0->'Result'->0->'MatchQuality'->'Street' ) as StreetMQ0,
(b->'Response'->'View'->0->'Result'->0->'MatchQuality'->'HouseNumber' ) as HousenumberMQ0,
(b->'Response'->'View'->0->'Result'->0->'MatchQuality'->'PostalCode' ) as PostalCodeMQ0,
(b->'Response'->'View'->0->'Result'->0->'MatchQuality'->'District' ) as DistsictMQ0

from aa
limit 100


select a,
(b->'Response'->'View'->0->'Result'->0->'Location'->'DisplayPosition'->'Latitude') as Lat0,
(b->'Response'->'View'->0->'Result'->0->'Location'->'DisplayPosition'->'Longitude' ) as Lon0,
(b->'Response'->'View'->0->'Result'->0->'Relevance' ) as relevance0,
(b->'Response'->'View'->0->'Result'->0->'MatchQuality' ) as mq,
(b->'Response'->'View'->0->'Result'->0->'MatchQuality'->'City' ) as CityMQ0,
(b->'Response'->'View'->0->'Result'->0->'MatchQuality'->'Street' ) as StreetMQ0,
(b->'Response'->'View'->0->'Result'->0->'MatchQuality'->'HouseNumber' ) as HousenumberMQ0,
(b->'Response'->'View'->0->'Result'->0->'MatchQuality'->'PostalCode' ) as PostalCodeMQ0,
(b->'Response'->'View'->0->'Result'->0->'MatchQuality'->'District' ) as DistsictMQ0,
(b->'Response'->'View'->0->'Result'->0->'Location'->'Address'->'Label') as AdressMQ0,

(b->'Response'->'View'->0->'Result'->1->'Location'->'DisplayPosition'->'Latitude') as Lat1,
(b->'Response'->'View'->0->'Result'->1->'Location'->'DisplayPosition'->'Longitude' ) as Lon1,
(b->'Response'->'View'->0->'Result'->1->'Relevance' ) as relevance1,
(b->'Response'->'View'->0->'Result'->1->'MatchQuality' ) as mq,
(b->'Response'->'View'->0->'Result'->1->'MatchQuality'->'City' ) as CityMQ1,
(b->'Response'->'View'->0->'Result'->1->'MatchQuality'->'Street' ) as StreetMQ1,
(b->'Response'->'View'->0->'Result'->1->'MatchQuality'->'HouseNumber' ) as HousenumberMQ1,
(b->'Response'->'View'->0->'Result'->1->'MatchQuality'->'PostalCode' ) as PostalCodeMQ1,
(b->'Response'->'View'->0->'Result'->1->'MatchQuality'->'District' ) as DistsictMQ1,
(b->'Response'->'View'->0->'Result'->1->'Location'->'Address'->'Label') as AdressMQ1,

(b->'Response'->'View'->0->'Result'->2->'Location'->'DisplayPosition'->'Latitude') as Lat2,
(b->'Response'->'View'->0->'Result'->2->'Location'->'DisplayPosition'->'Longitude' ) as Lon2,
(b->'Response'->'View'->0->'Result'->2->'Relevance' ) as relevance2,
(b->'Response'->'View'->0->'Result'->2->'MatchQuality' ) as mq,
(b->'Response'->'View'->0->'Result'->2->'MatchQuality'->'City' ) as CityMQ2,
(b->'Response'->'View'->0->'Result'->2->'MatchQuality'->'Street' ) as StreetMQ2,
(b->'Response'->'View'->0->'Result'->2->'MatchQuality'->'HouseNumber' ) as HousenumberMQ2,
(b->'Response'->'View'->0->'Result'->2->'MatchQuality'->'PostalCode' ) as PostalCodeMQ2,
(b->'Response'->'View'->0->'Result'->2->'MatchQuality'->'District' ) as DistsictMQ2,
(b->'Response'->'View'->0->'Result'->2->'Location'->'Address'->'Label') as AdressMQ2,

(b->'Response'->'View'->0->'Result'->3->'Location'->'DisplayPosition'->'Latitude') as Lat3,
(b->'Response'->'View'->0->'Result'->3->'Location'->'DisplayPosition'->'Longitude' ) as Lon3,
(b->'Response'->'View'->0->'Result'->3->'Relevance' ) as relevance3,
(b->'Response'->'View'->0->'Result'->3->'MatchQuality' ) as mq,
(b->'Response'->'View'->0->'Result'->3->'MatchQuality'->'City' ) as CityMQ3,
(b->'Response'->'View'->0->'Result'->3->'MatchQuality'->'Street' ) as StreetMQ3,
(b->'Response'->'View'->0->'Result'->3->'MatchQuality'->'HouseNumber' ) as HousenumberMQ3,
(b->'Response'->'View'->0->'Result'->3->'MatchQuality'->'PostalCode' ) as PostalCodeMQ3,
(b->'Response'->'View'->0->'Result'->3->'MatchQuality'->'District' ) as DistsictMQ3,
(b->'Response'->'View'->0->'Result'->3->'Location'->'Address'->'Label') as AdressMQ3,

from aa

limit 20

Geocoding

Immer wieder das gleiche Problem: Excel mit Anfahrtstelle  in form von Adressen mit Straße, Hausnummer, PLZ und Ort und dann die Frage: An welcher Geokoordinate befindet sich diese Adresse?

Hinweise von Anbietern gibt es hier(http://www.geotag.de/geocoding.html)

http://www.programmableweb.com/apitag/geocoding

http://blog.programmableweb.com/2012/06/21/7-free-geocoding-apis-google-bing-yahoo-and-mapquest/

http://open.mapquestapi.com/nominatim/

http://www.bing.com/toolbox/developers

https://www.bingmapsportal.com/

https://www.bingmapsportal.com/application/Index/1181132?status=Created

http://www.microsoft.com/maps/choose-your-bing-maps-API.aspx

http://developer.here.com/javascript_api_explorer

http://de.mygeoposition.com/

http://www.terramapserver.de/de/index.html

http://me4bruno.wordpress.com/2012/02/23/kostenlose-karten-und-geocoder-welchen-anbieter-kann-ich-nutzen/

https://geoservices.tamu.edu/Services/Geocode/About/GeocoderList.aspx

http://www.geocode.com/store/geocoding/index.cfm

http://www.geonames.org/

http://www.geomarketing.at/index.php/dienstleistungen23/geocodierung