Becoming a Miner

1. Becoming a Miner

2. Session #2Becoming a Miner • Features of the State Mobile Template • Configuring Google Fusion Tables (GFT) • Changing the Application

3. Home Page Features • “Set My Location” • “Search by County/City” • Location based map

4. Home Page Features • Top 3 results • Direct links to results

5. Home Page Features • Social media links

6. Home Page Features • “Contact Us” • “Regular Site”

7. Contact Us • Location and address • Working phone link

8. Select a County • “Back” • “CA.GOV” • “Home” • County List • Counts

9. Select a County • “Back to Top”

10. Select a City • Empty spaces at end

11. Results • Details CSS class • Bold text <span>

12. Map Result • Full screen map

13. Map Result • Address and location • Working phone link

14. Map Result • Google street view

15. Create a Google Fusion Table • Can be done from Google Drive

16. Review Your Table

17. Assign Location columns • Latitude/Longitude required for mapping

18. Test Your Map

19. Get Fusion Table ID • Encrypted ID from “File->About this table”

20. Enable Services • https://code.google.com/apis/console/ • “Fusion Tables API” • “Google Maps API v3” (Not v2)

21. Get your API Key

22. The California Mobile Gallery

23. Download the Mobile Template

24. Template Contents • ASP version • C# version • PHP version • VB.Net version

25. The Project

26. What you will work with • ApiKeys.resx • Dataset (dsMain.xsd) • MyData class • Default Page • Detail Page • Mobile.master • ContactUs Page • SqlQueries.resx

27. Update API Keys Resource File • Replace “GoogleApiKey” with API Key • Replace “TablePrimary” with Encrypted ID • Leave “TableCity”

28. Update Dataset • Match your columns

29. Update Classes • Update typed dataset column names

30. Update Text • Change text in ASPX and master pages to match your department

31. Update ContactUs Class • Change the “lat” and “lng” to your office location.

32. Executing Queries • sqlGenericList: Returns the top X rows by distance.SELECT * FROM {0} ORDER BY ST_DISTANCE({1}, LATLNG({2},{3})) LIMIT {4}

33. SqlQueries Resource File • sqlCityLookup: Used to query a single city by distance from the shared city table.SELECT CITY FROM {0} ORDER BY ST_DISTANCE(LATITUDE, LATLNG({1},{2})) LIMIT 1 • sqlGenericDetail: Bring all columns for a single row by a single key value.SELECT * FROM {0} WHERE {1} = '{2}' LIMIT 1 • sqlGenericList: Returns the top X rows by distance.SELECT * FROM {0} ORDER BY ST_DISTANCE({1}, LATLNG({2},{3})) LIMIT {4} • sqlGroupByOne: Group by one column. (ex, county)SELECT {1}, COUNT() FROM {0} GROUP BY {1} ORDER BY {1} • sqlGroupByTwo:Group by two columns filtered by one. (ex, county/city where county=X)SELECT {1}, {2}, COUNT() FROM {0} WHERE {2} = '{3}' GROUP BY {1}, {2} ORDER BY {1} • sqlWhereTwo: Returns all rows matching two filters ordered by one column.SELECT * FROM {0} WHERE {1} = '{2}' AND {3} = '{4}' ORDER BY {5} • sqlZipLookup: Return City/LAT/LON where zip code matches.Select CITY, LATITUDE, LONGITUDE from {0} where ZIP_CODE = '{1}' Limit 1

34. How Map Script is Added • litInitScript.Text += GetMapScript(dt, 1, dt.LATITUDEColumn, dt.LONGITUDEColumn);

35. Questions? • Carter.medlin@state.ca.gov