-1

I have

1) Shapefile with the subzones of the Singapore Map (segmented areas of the country map)

2) Excel Spreadsheet with the latitude and longitude of each location

How do I determine the subzone of the data on the excel spreadsheet based on the latitude and longitude data given.

Thank you so much in advance!

Rahul Sharma
  • 2,867
  • 2
  • 27
  • 40
QY Wong
  • 51
  • 2
  • 1
    Possible duplicate of [How to find which polygon a point belong to via sf](https://stackoverflow.com/questions/43456524/how-to-find-which-polygon-a-point-belong-to-via-sf) – SymbolixAU Jan 03 '18 at 05:31

2 Answers2

2

ArcGIS is a very capable GIS package, which provides a range of tools for matching geocoded points to other spatial datasets. I'd suggest you check out the on-line help for spatial joins in particular (http://desktop.arcgis.com/en/arcmap/10.3/manage-data/tables/about-joining-the-attributes-of-features-by-their-location.htm). If your Excel file has only co-ordinates the spatial join or built-in point-in-polygon tools are your most likely options. If you have a subzone name or identifier in your Excel data you could join directly to your shapefile data using that field as the common identifier for the join instead.

As ArcGIS has always had the tools to do such point matching you should not need to use R to do these tasks instead, though there are specialist spatial libraries available which provide point-in-polygon functions amongst many others.

Stewart Ross
  • 1,034
  • 1
  • 8
  • 10
0

You should not need to waste $1,500+ to do things like this.

Let's say we have a Singapore shapefile with (amongst other things) subzone boundaries:

library(sf)
library(tidyverse)

# read in shapefile
singapore <- st_read("~/data/master-plan-2014-subzone-boundary-no-sea-shp/MP14_SUBZONE_NO_SEA_PL.shp", quiet=TRUE, stringsAsFactors=FALSE)

# check CRS
st_crs(singapore)
## Coordinate Reference System:
##   No EPSG code
##   proj4string: "+proj=tmerc +lat_0=1.366666666666667 +lon_0=103.8333333333333 +k=1 +x_0=28001.642 +y_0=38744.572 +datum=WGS84 +units=m +no_defs"

# your spreadsheet is likely in lat/lng but this shapefile isn't so we'll convert the CRS to lat/lng
singapore <- st_transform(singapore, 4326)

st_crs(singapore)
## Coordinate Reference System:
##   EPSG: 4326 
##   proj4string: "+proj=longlat +datum=WGS84 +no_defs"

# to prove this is, indeed, a singapore map
plot(singapore, max.plot=1)

enter image description here

Here's a "spreadsheet" (use readxl::read_excel() if you want to read from Excel xlsx directly vs export it to CSV) of recycling centers in Sinagpore:

centers <- read_csv("~/data/recycl.csv")
glimpse(centers)
## Observations: 407
## Variables: 10
## $ lng             <dbl> 104.0055, 103.7677, 103.7456, 103.7361, 103.8106, 103.962...
## $ lat             <dbl> 1.316764, 1.296245, 1.319204, 1.380412, 1.286512, 1.33355...
## $ inc_crc         <chr> "F8907D68D7EB64A1", "ED1F74DC805CEC8B", "F48D575631DCFECB...
## $ name            <chr> "RENEW (Recycling Nation's Electronic Waste)", "RENEW (Re...
## $ block_house_num <chr> "10", "84", "698", "3", "2", "1", "1", "1", "357", "50", ...
## $ bldg_name       <chr> "Changi Water Reclamation Plant", "Clementi Woods", "Comm...
## $ floor           <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, N...
## $ post_code       <int> 498785, 126811, 608784, 689814, 159047, 486036, 39393, 55...
## $ street          <chr> "Changi East Close", "West Coast Road , Clementi Woods Co...
## $ unit            <chr> "(Lobby)", "#B1-01 (Management Office)", "(School foyer)"...

We can convert that to a simple features object:

map2(centers$lng, centers$lat, ~st_point(c(.x, .y))) %>% 
  st_sfc(crs = 4326) %>% 
  st_sf(centers[,-(1:2)], .) -> centers_sf

then see which subzone each one belongs in:

bind_cols(
  centers,
  singapore[as.numeric(st_within(centers_sf, singapore)),]
) %>% 
  select(lng, lat, inc_crc, subzone_name=SUBZONE_N) %>% # being selective of fields to display purely for brevity
  mutate(subzone_name = str_to_title(subzone_name))
## # A tibble: 407 x 4
##         lng      lat          inc_crc               subzone_name
##       <dbl>    <dbl>            <chr>                      <chr>
##  1 104.0055 1.316764 F8907D68D7EB64A1             Changi Airport
##  2 103.7677 1.296245 ED1F74DC805CEC8B             Clementi Woods
##  3 103.7456 1.319204 F48D575631DCFECB              Teban Gardens
##  4 103.7361 1.380412 1F910E0086FD4798                 Peng Siang
##  5 103.8106 1.286512 55A0B9E7CBD34AFE             Alexandra Hill
##  6 103.9624 1.333555 C664D09D9CD5325F                      Xilin
##  7 103.8542 1.292778 411F79EAAECFE609                  City Hall
##  8 103.8712 1.375876 F4516742CFD4228E Serangoon North Ind Estate
##  9 103.8175 1.293319 B05B32DF52D922E7            Alexandra North
## 10 103.9199 1.335878 58E9EAF06206C772            Bedok Reservoir
## # ... with 397 more rows

Take the $1,500+ you saved and go on a nice vacation.

hrbrmstr
  • 77,368
  • 11
  • 139
  • 205