Problems with shp2dta and spmap: mistake that leads to wrong connections on map?

[Guest]

Dear all

For my master thesis, I am working with Stata, and more specifically with geographical maps in Stata. This means that I make use of shp2dta and especially spmap quite a bit.

I stumbled across a problem after I downloaded the .shp and .dbf files of the European Union from the website Eurostat. shp2dta let me know that the .dbf file was not suited, so after some research, I opened it in MS excel, saved it using MS access, which made it so that shp2dta ran without a problem. I added this piece of information as it may (or may not) become relevant for my current issue.

When creating the map using spmap, a rather odd anomaly occured. As I cannot find the right words to describe it exactly, I'll add a screenshot. The coordinates file seems to corrupted somehow...

Does anyone have an idea of what the origin of this problem could be?

Thank you in advance
Guest

[Jorrit Gosens]

Difficult to say how to fix this file, which does indeed seem broken. Re-doing it properly is probably easier than fixing the broken file.

There was a recent, similar discussion here, which has three other options.
Edit: There's no need for exporting to MS Access, just importing the dbf in excel and saving as xls is enough to work around it. Then import this xls into stata and save as coordinates.dta set. for importing dbf into excel see: https://smallbusiness.chron.com/conv...xls-55554.html


If youre on Stata 15 you could also try spshape2dta
and there's also the option to import dbase.
See more on those options here: https://www.statalist.org/forums/for...efile-into-dta

If that doesn't help you get there, give an exact location of where the shp/dbf files are located and someone will be able to help generate proper dta files.

I also notified the author of shp2dta, btw, and he said he'd look into it, but no further notices on that front since.

[Guest]

Thanks Jorrit, as I am working on Stata 14, it is sadly not possible for me to use spshape2dta or export dbase. So I'm afraid I can't follow your advice. For shp2dta I need a .dbf file and it seems the troubles originate from the conversion of the .shp file.

If you, or anyone else, have any more ideas, please feel free to share them.

Edit: This is the webpage I downloaded the files from.

[Jorrit Gosens]

You can use shp2dta to create the coordinates file. Do so by running the shp2dta command, even if it gives an error on the dbf, it will still create the coordinates.dta file.
For the dbf, import it into excel, save as csv or xls, and import that into Stata, simply with the import csv or xls functionality, and then save as database.dta
This file will now have the exact same layout as the database.dta that would be created by the shp2dta command

Edit: noet that I mixed up the coordinates and database files in post #2. Use excel to read the database file, not the coordinates file

[Guest]

Ok, I tried it and now I know this, it already makes it faster to work on things in the future.

However, the shapefile still seems to be corrupted somehow after doing it this way; the resulting map is the same as in my first post.

[Jorrit Gosens]

Oh, that's too bad. Bit of a long way around to get to the same result then. I imagined something had gone wrong with the process for translation to dta files.
An easier way might be to find another shapefile/dbf?


Edit: which map are you using exactly? Can you give an url? Just to see if I can create a functional map with spshape2dta. If that is not the case, then we know the map is faulty, and not our process of transforming it

[Guest]

Here is the link to the zip file. I used the file NUTS_BN_10M_2016_3035, and tried out others as well, they all give similar results.

As far as I could see, the created coordinates.dta files do have some peculiarities. The units in this file normally should end with the same coordinates as they start with according to the spmap helpfile, however, in this file they often do not. I'll add a short cutout.

_ID    _X    _Y
1        
1    6722287    2044703
1    6730990    2049698
1    6756062    2051013
2        
2    2699182    1790554
2    2706201    1789092
2    2712970    1778409
2    2722828    1773050
2    2732957    1779371
2    2755788    1784096
2    2776212    1783298
3        
3    2777974    1743032
3    2779122    1770686
3    2776212    1783298

[Guest]

In the meantime, I realized my mistake. I used the NUTS_BN instead of the NUTS_RG.

For future reference, the explanation for this can be found here, under 1.1.2.4 Entity type.

[Jorrit Gosens]

Alright, good to hear this is solved. I would not have figured out the difference between the two formats, most likely.

[Guest]

In any case, thanks a bunch for your help!

[Antonis Mavropoulos]

How did you create the identification number in the database file?
Creating an ascending id using:
gen id=_n

does seem to mess up the id between database and coordinate file.

[Jorrit Gosens]

You very most usually shouldnt need to create an ID yourself.
If its a properly created file it should have correctly corresponding IDs in each of the files. It may be that its not called ID but e.g. region or country number or something.