Angewandte IT in Logistik & Healthcare

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

14.06.2013

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

05.04.2013

Sourceforge - Alternativen - selber betreiben (SCM)

Beim Aufsetzten von neuen Softwareprojekten ist klar, dass es ein Quellcodeverwaltungsystem her muss, Projektinhalte wachen und werden im Wiki dokumentiert, ein Bugtracker soll die Fehler und Features verwalten an besten wie Projektmanagement durch Todolisten. Welche Closed- und Opensource-Möglichkeiten gibt es?

Softwareprojekte zum Hosten von Softwareprojekten:

Savannah: https://savannah.gnu.org/

LibreSource: http://dev.libresource.org

Redmine: http://www.redmine.org/

InDefero: http://www.indefero.net/

FusionForge: http://fusionforge.org/

Gforge: http://gforge.org/gf/

Links:

http://savannah.gnu.org/maintenance/WhyChooseSavannah