Destring Latitude and Longitude

[Alessandro Corvasce]

Goodmoning everybody,
I am facing some problems with "destring". I have a dataset that contains latitude and longitude of crimes:

* Example generated by -dataex-. For more info, type help dataex
clear
input str11 casenumber str28 offensecategory str12 opendatalat str14 opendatalon long(lati longi) float yr
"15-362208" "Robbery"             "45,531363086" "-122,675124892" 26491 32867 2015
"15-903260" "Larceny Offenses"    "45,523105666" "-122,668684537" 22498 31688 2015
"15-251220" "Burglary"            "45,557132785" "-122,667063740" 35332 31463 2015
"15-209514" "Larceny Offenses"    "45,575075503" "-122,667241336" 39474 31487 2015
"15-907967" "Larceny Offenses"    "45,575075503" "-122,667241336" 39474 31487 2015
"15-196093" "Larceny Offenses"    "45,532573192" "-122,667711130" 27028 31525 2015
"15-240690" "Larceny Offenses"    "45,532573192" "-122,667711130" 27028 31525 2015
"15-334910" "Assault Offenses"    "45,532573192" "-122,667711130" 27028 31525 2015
"15-351486" "Larceny Offenses"    "45,532573192" "-122,667711130" 27028 31525 2015
"15-371400" "Larceny Offenses"    "45,532573192" "-122,667711130" 27028 31525 2015
"15-385808" "Larceny Offenses"    "45,532573192" "-122,667711130" 27028 31525 2015
"15-905826" "Larceny Offenses"    "45,532573192" "-122,667711130" 27028 31525 2015
"15-906327" "Larceny Offenses"    "45,532573192" "-122,667711130" 27028 31525 2015
"15-906424" "Larceny Offenses"    "45,532573192" "-122,667711130" 27028 31525 2015
"15-906654" "Larceny Offenses"    "45,532573192" "-122,667711130" 27028 31525 2015
"15-906935" "Larceny Offenses"    "45,532573192" "-122,667711130" 27028 31525 2015
"15-907149" "Larceny Offenses"    "45,532573192" "-122,667711130" 27028 31525 2015
"15-908084" "Larceny Offenses"    "45,532573192" "-122,667711130" 27028 31525 2015
"15-907789" "Larceny Offenses"    "45,562672554" "-122,667189401" 37070 31481 2015
"15-214999" "Burglary"            "45,577260280" "-122,667195886" 39964 31482 2015
"15-902644" "Larceny Offenses"    "45,574476647" "-122,667280963" 39373 31492 2015
"15-194978" "Motor Vehicle Theft" "45,544614914" "-122,666991857" 31496 31454 2015
"15-335289" "Larceny Offenses"    "45,569996353" "-122,667133238" 38652 31471 2015
"15-900998" "Larceny Offenses"    "45,560555499" "-122,667280390" 36480 31491 2015
"15-904708" "Larceny Offenses"    "45,538006251" "-122,666950384" 29643 31444 2015
"15-207751" "Larceny Offenses"    "45,559771238" "-122,667258245" 36244 31488 2015
"15-900577" "Larceny Offenses"    "45,559771238" "-122,667258245" 36244 31488 2015
"15-194125" "Assault Offenses"    "45,534318273" "-122,666977469" 27860 31449 2015
"15-374683" "Larceny Offenses"    "45,534318273" "-122,666977469" 27860 31449 2015
"15-904424" "Larceny Offenses"    "45,557939422" "-122,667063303" 35590 31462 2015
"15-909105" "Larceny Offenses"    "45,523312485" "-122,663680806" 22613 30805 2015
"15-906115" "Larceny Offenses"    "45,523284464" "-122,659628288" 22603 29778 2015
"15-905713" "Larceny Offenses"    "45,523281586" "-122,658613365" 22599 29474 2015
"15-149039" "Larceny Offenses"    "45,523267167" "-122,654557471" 22588 28291 2015
"15-247275" "Larceny Offenses"    "45,523267167" "-122,654557471" 22588 28291 2015
"15-409511" "Larceny Offenses"    "45,523267167" "-122,654557471" 22588 28291 2015
"15-905779" "Larceny Offenses"    "45,523264646" "-122,653667464" 22586 28038 2015
"15-654514" "Larceny Offenses"    "45,523247159" "-122,648592750" 22573 26470 2015
"15-408945" "Larceny Offenses"    "45,523232065" "-122,637191212" 22558 23620 2015
"15-907093" "Larceny Offenses"    "45,523243798" "-122,635969985" 22570 23319 2015
"15-426416" "Motor Vehicle Theft" "45,523237965" "-122,633034639" 22567 22621 2015
"15-908598" "Larceny Offenses"    "45,523236959" "-122,631758290" 22565 22310 2015
"15-907213" "Larceny Offenses"    "45,523577062" "-122,617965779" 22732 19302 2015
"15-370058" "Burglary"            "45,523389312" "-122,615050980" 22649 18655 2015
"15-370058" "Larceny Offenses"    "45,523389312" "-122,615050980" 22649 18655 2015
"15-900573" "Larceny Offenses"    "45,523234282" "-122,606314052" 22561 16779 2015
"15-905688" "Larceny Offenses"    "45,523340557" "-122,604257177" 22622 16380 2015
"15-901107" "Larceny Offenses"    "45,523207652" "-122,602374884" 22545 16024 2015
"15-901752" "Larceny Offenses"    "45,523191486" "-122,599657764" 22539 15430 2015
"15-377928" "Burglary"            "45,523184803" "-122,598521731" 22531 15160 2015
"15-160324" "Burglary"            "45,523368250" "-122,597189762" 22636 14894 2015
"15-905037" "Larceny Offenses"    "45,523453489" "-122,589800522" 22668 13493 2015
"15-903966" "Larceny Offenses"    "45,523360627" "-122,588872082" 22630 13251 2015
"15-410087" "Larceny Offenses"    "45,523110907" "-122,583586007" 22501 12154 2015
"15-410087" "Motor Vehicle Theft" "45,523110907" "-122,583586007" 22501 12154 2015
"15-289379" "Larceny Offenses"    "45,523186135" "-122,581403074" 22534 11672 2015
"15-200918" "Larceny Offenses"    "45,523172679" "-122,579115410" 22530 11221 2015
"15-245036" "Larceny Offenses"    "45,523172679" "-122,579115410" 22530 11221 2015
"15-405885" "Larceny Offenses"    "45,523172679" "-122,579115410" 22530 11221 2015
"15-437809" "Larceny Offenses"    "45,523172679" "-122,579115410" 22530 11221 2015
"15-410883" "Larceny Offenses"    "45,523220769" "-122,577973586" 22552 10797 2015
"15-175086" "Burglary"            "45,523271836" "-122,576059070" 22593 10445 2015
"15-402485" "Burglary"            "45,523271349" "-122,575934156" 22592 10421 2015
"15-227286" "Larceny Offenses"    "45,522959585" "-122,558339788" 22412  7823 2015
"15-339033" "Larceny Offenses"    "45,522959585" "-122,558339788" 22412  7823 2015
"15-339074" "Larceny Offenses"    "45,522959585" "-122,558339788" 22412  7823 2015
"15-305185" "Burglary"            "45,522948279" "-122,546685106" 22404  6259 2015
"15-409253" "Larceny Offenses"    "45,522951540" "-122,546810119" 22406  6287 2015
"15-222545" "Burglary"            "45,522959244" "-122,542985493" 22411  5780 2015
"15-288953" "Burglary"            "45,522897265" "-122,537823560" 22331  5137 2015
"15-281246" "Larceny Offenses"    "45,523242038" "-122,534623788" 22569  4659 2015
"15-438873" "Assault Offenses"    "45,522753697" "-122,532607987" 22198  4412 2015
"15-907962" "Larceny Offenses"    "45,523021749" "-122,527840373" 22446  3838 2015
"15-333522" "Larceny Offenses"    "45,522868791" "-122,523444111" 22298  3320 2015
"15-902892" "Larceny Offenses"    "45,522848631" "-122,521004063" 22277  3067 2015
"15-218079" "Larceny Offenses"    "45,522806910" "-122,515725831" 22225  2550 2015
"15-264919" "Motor Vehicle Theft" "45,522857459" "-122,512058882" 22285  2222 2015
"15-236484" "Motor Vehicle Theft" "45,522842697" "-122,510707951" 22271  2140 2015
"15-330404" "Burglary"            "45,522725250" "-122,508010789" 22179  1880 2015
"15-335224" "Motor Vehicle Theft" "45,522725250" "-122,508010789" 22179  1880 2015
"15-903475" "Larceny Offenses"    "45,522725250" "-122,508010789" 22179  1880 2015
"15-224226" "Assault Offenses"    "45,522743396" "-122,498945000" 22189  1063 2015
"15-404338" "Burglary"            "45,522743396" "-122,498945000" 22189  1063 2015
"15-404338" "Motor Vehicle Theft" "45,522743396" "-122,498945000" 22189  1063 2015
"15-906283" "Larceny Offenses"    "45,522396705" "-122,458858388" 21967    43 2015
"15-900026" "Larceny Offenses"    "45,578670182" "-122,665901858" 40197 31257 2015
"15-209905" "Larceny Offenses"    "45,535141851" "-122,666224214" 28310 31289 2015
"15-283225" "Larceny Offenses"    "45,535141851" "-122,666224214" 28310 31289 2015
"15-362990" "Assault Offenses"    "45,535141851" "-122,666224214" 28310 31289 2015
"15-380656" "Larceny Offenses"    "45,535054043" "-122,666224768" 28216 31290 2015
"15-430220" "Motor Vehicle Theft" "45,535141992" "-122,665197480" 28311 31093 2015
"15-900914" "Larceny Offenses"    "45,535141851" "-122,666224214" 28310 31289 2015
"15-196058" "Larceny Offenses"    "45,575161117" "-122,665900700" 39492 31253 2015
"15-201073" "Motor Vehicle Theft" "45,582807171" "-122,667087444" 40915 31467 2015
"15-245412" "Larceny Offenses"    "45,582219868" "-122,665056707" 40782 31074 2015
"15-392104" "Larceny Offenses"    "45,546735979" "-122,665378183" 32096 31114 2015
"15-902118" "Larceny Offenses"    "45,571834206" "-122,666570599" 38934 31311 2015
"15-409254" "Motor Vehicle Theft" "45,546022556" "-122,665382201" 31896 31119 2015
"15-177004" "Larceny Offenses"    "45,579356107" "-122,665900702" 40309 31254 2015
"15-177004" "Motor Vehicle Theft" "45,579356107" "-122,665900702" 40309 31254 2015
end
label values lati lati
label def lati 21967 "45,522396705", modify
label def lati 22179 "45,522725250", modify
label def lati 22189 "45,522743396", modify
label def lati 22198 "45,522753697", modify
label def lati 22225 "45,522806910", modify
label def lati 22271 "45,522842697", modify
label def lati 22277 "45,522848631", modify
label def lati 22285 "45,522857459", modify
label def lati 22298 "45,522868791", modify
label def lati 22331 "45,522897265", modify
label def lati 22404 "45,522948279", modify
label def lati 22406 "45,522951540", modify
label def lati 22411 "45,522959244", modify
label def lati 22412 "45,522959585", modify
label def lati 22446 "45,523021749", modify
label def lati 22498 "45,523105666", modify
label def lati 22501 "45,523110907", modify
label def lati 22530 "45,523172679", modify
label def lati 22531 "45,523184803", modify
label def lati 22534 "45,523186135", modify
label def lati 22539 "45,523191486", modify
label def lati 22545 "45,523207652", modify
label def lati 22552 "45,523220769", modify
label def lati 22558 "45,523232065", modify
label def lati 22561 "45,523234282", modify
label def lati 22565 "45,523236959", modify
label def lati 22567 "45,523237965", modify
label def lati 22569 "45,523242038", modify
label def lati 22570 "45,523243798", modify
label def lati 22573 "45,523247159", modify
label def lati 22586 "45,523264646", modify
label def lati 22588 "45,523267167", modify
label def lati 22592 "45,523271349", modify
label def lati 22593 "45,523271836", modify
label def lati 22599 "45,523281586", modify
label def lati 22603 "45,523284464", modify
label def lati 22613 "45,523312485", modify
label def lati 22622 "45,523340557", modify
label def lati 22630 "45,523360627", modify
label def lati 22636 "45,523368250", modify
label def lati 22649 "45,523389312", modify
label def lati 22668 "45,523453489", modify
label def lati 22732 "45,523577062", modify
label def lati 26491 "45,531363086", modify
label def lati 27028 "45,532573192", modify
label def lati 27860 "45,534318273", modify
label def lati 28216 "45,535054043", modify
label def lati 28310 "45,535141851", modify
label def lati 28311 "45,535141992", modify
label def lati 29643 "45,538006251", modify
label def lati 31496 "45,544614914", modify
label def lati 31896 "45,546022556", modify
label def lati 32096 "45,546735979", modify
label def lati 35332 "45,557132785", modify
label def lati 35590 "45,557939422", modify
label def lati 36244 "45,559771238", modify
label def lati 36480 "45,560555499", modify
label def lati 37070 "45,562672554", modify
label def lati 38652 "45,569996353", modify
label def lati 38934 "45,571834206", modify
label def lati 39373 "45,574476647", modify
label def lati 39474 "45,575075503", modify
label def lati 39492 "45,575161117", modify
label def lati 39964 "45,577260280", modify
label def lati 40197 "45,578670182", modify
label def lati 40309 "45,579356107", modify
label def lati 40782 "45,582219868", modify
label def lati 40915 "45,582807171", modify
label values longi longi
label def longi 43 "-122,458858388", modify
label def longi 1063 "-122,498945000", modify
label def longi 1880 "-122,508010789", modify
label def longi 2140 "-122,510707951", modify
label def longi 2222 "-122,512058882", modify
label def longi 2550 "-122,515725831", modify
label def longi 3067 "-122,521004063", modify
label def longi 3320 "-122,523444111", modify
label def longi 3838 "-122,527840373", modify
label def longi 4412 "-122,532607987", modify
label def longi 4659 "-122,534623788", modify
label def longi 5137 "-122,537823560", modify
label def longi 5780 "-122,542985493", modify
label def longi 6259 "-122,546685106", modify
label def longi 6287 "-122,546810119", modify
label def longi 7823 "-122,558339788", modify
label def longi 10421 "-122,575934156", modify
label def longi 10445 "-122,576059070", modify
label def longi 10797 "-122,577973586", modify
label def longi 11221 "-122,579115410", modify
label def longi 11672 "-122,581403074", modify
label def longi 12154 "-122,583586007", modify
label def longi 13251 "-122,588872082", modify
label def longi 13493 "-122,589800522", modify
label def longi 14894 "-122,597189762", modify
label def longi 15160 "-122,598521731", modify
label def longi 15430 "-122,599657764", modify
label def longi 16024 "-122,602374884", modify
label def longi 16380 "-122,604257177", modify
label def longi 16779 "-122,606314052", modify
label def longi 18655 "-122,615050980", modify
label def longi 19302 "-122,617965779", modify
label def longi 22310 "-122,631758290", modify
label def longi 22621 "-122,633034639", modify
label def longi 23319 "-122,635969985", modify
label def longi 23620 "-122,637191212", modify
label def longi 26470 "-122,648592750", modify
label def longi 28038 "-122,653667464", modify
label def longi 28291 "-122,654557471", modify
label def longi 29474 "-122,658613365", modify
label def longi 29778 "-122,659628288", modify
label def longi 30805 "-122,663680806", modify
label def longi 31074 "-122,665056707", modify
label def longi 31093 "-122,665197480", modify
label def longi 31114 "-122,665378183", modify
label def longi 31119 "-122,665382201", modify
label def longi 31253 "-122,665900700", modify
label def longi 31254 "-122,665900702", modify
label def longi 31257 "-122,665901858", modify
label def longi 31289 "-122,666224214", modify
label def longi 31290 "-122,666224768", modify
label def longi 31311 "-122,666570599", modify
label def longi 31444 "-122,666950384", modify
label def longi 31449 "-122,666977469", modify
label def longi 31454 "-122,666991857", modify
label def longi 31462 "-122,667063303", modify
label def longi 31463 "-122,667063740", modify
label def longi 31467 "-122,667087444", modify
label def longi 31471 "-122,667133238", modify
label def longi 31481 "-122,667189401", modify
label def longi 31482 "-122,667195886", modify
label def longi 31487 "-122,667241336", modify
label def longi 31488 "-122,667258245", modify
label def longi 31491 "-122,667280390", modify
label def longi 31492 "-122,667280963", modify
label def longi 31525 "-122,667711130", modify
label def longi 31688 "-122,668684537", modify
label def longi 32867 "-122,675124892", modify

I need to have lat and long in numeric format in order to implement "geoinpoly" and geocode each records to its census tract.
However when I run "destring opendatalat, generate(latitude)" I get the following message from Stata "opendatalat: contains nonnumeric characters; no generate"
I have tried to use "encode opendatalat, gen(lati)" but the results does not fit with "geoinpoly" command (latitude lati must be between -90 and 90).
Do you have any suggestions?
Thank you!!

[Alessandro Corvasce]

In addition, if I try to use the "force" option it generates only missing values, so I guess the problem it is not generated only by some observations. I have also tried "list opendatalat if missing(real( opendatalat ))" and in fact it reports problem with all the observation in the dataset.

[Andrew Musau]

It does not make sense to use encode here. You want the option -dpcomma- as the string coordinates use commas as decimal separators.

destring opendatalat, gen(latitude) dpcomma

[Mike Lacy]

(crossed with previous posting)
Read about and use the -dpcomma- option, as described in -help destring-, to account for the use of the comma to indicate decimal places.

destring opendatalat, gen(latitude) dpcomma

It's possible that, in addition to the use of the comma, you might have other stray non-numeric characters in some observations of your latitude and longitude strings, but if that is true, you can use the -force- option of -destring- and then investigate these problem observations to find out what is going on.

You very definitely do *not* want to use -encode-. Per -help encode-:"Do not use encode if varname contains numbers that merely happen to be stored as strings."

[Alessandro Corvasce]

thank you Andrew and Mike, it was just a matter of comma!

[Sergiy Radyakin]

Alessandro Corvasce did you know it is also super-easy to export this data in the form of a geojson to visualize it? See:

This requires geotools package.
See geotools homepage for installations and usage instructions.
To create the above, run:

drop lati longi
destring opendatalat, dpcomma generate(lati)
destring opendatalon, dpcomma generate(longi)

geoutils ccombine , latitude(lati) longitude(longi) generate(geo)
drop lati longi opendatalat opendatalon

generate marker = ""
replace marker="police"       if offensecategory=="Assault Offenses"
replace marker="warehouse"    if offensecategory=="Burglary"
replace marker="fire-station" if offensecategory=="Larceny Offenses"
replace marker="car"          if offensecategory=="Motor Vehicle Theft"
replace marker="pitch"        if offensecategory=="Robbery"

generate xmcolor = ""
replace xmcolor="orange"      if offensecategory=="Assault Offenses"
replace xmcolor="blue"        if offensecategory=="Burglary"
replace xmcolor="red"         if offensecategory=="Larceny Offenses"
replace xmcolor="green"       if offensecategory=="Motor Vehicle Theft"
replace xmcolor="yellow"      if offensecategory=="Robbery"

geojson save geo using "c:\temp\crime.geojson", replace ///
   mcolorv(xmcolor) msize(medium) msymbolv(marker) ///
   scolor(#ff8000) swidth(1) sopacity(50/100) ///
   fcolor(#0080ff) fopacity(50/100) features("Point")

// END OF FILE

Then open the resulting geojson file in your favorite GIS system or use https://geojson.io online.

Best, Sergiy Radyakin

[Alessandro Corvasce]

That's a great hint Sergiy! I am going to use the geotools package for sure.
Thank you very much