Google Retiring Fusion Tables Dec 2019

[S4GRU]

I just got the same email.  Hopefully it will hold all our pins.  

Robert

[S4GRU]

I've been trying to use Mapbox the past few days.  I think I've given up.  It's too complicated for me.  I am not a developer.

Robert

[Trip]

I've never used MapBox, but I'm probably more of a developer than you are, even if I also wouldn't call myself one.  What kind of problems is it giving you?  (We can take this to a PM if you want.)

- Trip

[S4GRU]

4 minutes ago, Trip said:

I've never used MapBox, but I'm probably more of a developer than you are, even if I also wouldn't call myself one.  What kind of problems is it giving you?  (We can take this to a PM if you want.)

- Trip

It's just complex from my standpoint.  I managed to get a the Clearwire map data moved over.  So I have a bunch of pins.  But trying to get each marker to be called out as unique (not all the same type and color) and all of a sudden i need to write CSS and JSON's and I read tutorials and try messing with it and I have no idea what I'm doing.  It really shows how simple and beautiful a product that Fusion Tables was.

I don't want to go back to BatchGeo as it is expensive.  Like $100 per month.  MapBox is free for the first 25,000 unique visits, and scalable pricing beyond that.  I think we should be well under 25,000 visits per month.

Robert

[RAvirani]

31 minutes ago, S4GRU said:

It's just complex from my standpoint.  I managed to get a the Clearwire map data moved over.  So I have a bunch of pins.  But trying to get each marker to be called out as unique (not all the same type and color) and all of a sudden i need to write CSS and JSON's and I read tutorials and try messing with it and I have no idea what I'm doing.  It really shows how simple and beautiful a product that Fusion Tables was.

I don't want to go back to BatchGeo as it is expensive.  Like $100 per month.  MapBox is free for the first 25,000 unique visits, and scalable pricing beyond that.  I think we should be well under 25,000 visits per month.

Robert

When I heard about the fusiontables shutdown, I played with building a map solution that uses a SQL database (which can be edited row by row with a GUI) and the google maps API to display pins and data on a map. I did get it working although didn't really test it with a high volume of pins.

The problem with rendering a lot of individual pins is that it takes a lot of memory/processing power on the client side. Fusiontables got around this by rendering the pins as image tiles on the server side, and serving these to the user. I know that if I switch from exporting single data points to exporting KMLs, the pins can be rendered as tiles server-side, but I haven't really investigated this. 

Effectively, I'd be trying to create something very similar to fusiontables where you can edit rows in a GUI and display the data onto a map via server-side-rendered tiles. If you cannot find any other suitable map platforms, this is something I can look into...

Here is an example map I just threw together with a few M-MIMO sites in the greater Puget Sound area:

https://projects.viranifamily.net/mysql-maps/

[S4GRU]

4 minutes ago, RAvirani said:

When I heard about the fusiontables shutdown, I played with building a map solution that uses a SQL database (which can be edited row by row with a GUI) and the google maps API to display pins and data on a map. I did get it working although didn't really test it with a high volume of pins.

The problem with rendering a lot of individual pins is that it takes a lot of memory/processing power on the client side. Fusiontables got around this by rendering the pins as image tiles on the server side, and serving these to the user. I know that if I switch from exporting single data points to exporting KMLs, the pins can be rendered as tiles server-side, but I haven't really investigated this. 

Effectively, I'd be trying to create something very similar to fusiontables where you can edit rows in a GUI and display the data onto a map via server-side-rendered tiles. If you cannot find any other suitable map platforms, this is something I can look into...

Here is an example map I just threw together with a few M-MIMO sites in the greater Puget Sound area:

https://projects.viranifamily.net/mysql-maps/

I would love to do something like this. Performance does matter though, so server side would be critical.  I use a SQL editor all the time and feel comfortable doing that too.  So a custom GUI or SQL for editing is fine with me either way.  I would just want to be able to keep secure who can edit, like we do now through Google Doc permissions.  And if we just handle that on the URL side, that might be secure enough.  Thank you for looking into this further!

Robert

[Trip]

1 minute ago, RAvirani said:

When I heard about the fusiontables shutdown, I played with building a map solution that uses a SQL database (which can be edited row by row with a GUI) and the google maps API to display pins and data on a map. I did get it working although didn't really test it with a high volume of pins.

The problem with rendering a lot of individual pins is that it takes a lot of memory/processing power on the client side. Fusiontables got around this by rendering the pins as image tiles on the server side, and serving these to the user. I know that it I switch from exporting single data points to exporting KMLs, the pins can be rendered as tiles server-side, but I haven't really investigated this. 

Effectively, I'd be trying to create something very similar to fusiontables where you can edit rows in a GUI and display the data onto a map via server-side-rendered tiles. If you cannot find any other suitable map platforms, this is something I can look into...

Here is an example map I just threw together with a few M-MIMO sites in the greater Puget Sound area:

https://projects.viranifamily.net/mysql-maps/

The problem with this example is that the Google Maps API is also heavily limited now.  I had to switch my website over to Leaflet.  So even what you've written so far would probably have to be rewritten.

I've written code that does what we're discussing here in other projects, but the editing ability spread across multiple users in a friendly manner is where I run into problems.  I've only ever written code that has one editor--myself. 

- Trip

[RAvirani]

5 minutes ago, Trip said:

The problem with this example is that the Google Maps API is also heavily limited now.  I had to switch my website over to Leaflet.  So even what you've written so far would probably have to be rewritten.

I've written code that does what we're discussing here in other projects, but the editing ability spread across multiple users in a friendly manner is where I run into problems.  I've only ever written code that has one editor--myself. 

- Trip

The Google Maps Javascript API is $7 per 1000 loads, and you get a $200 per month of free usage. As long as we stay below roughly 28,500 loads per month, we shouldn't have issues using the Google Maps API.  

https://cloud.google.com/maps-platform/pricing/

[Trip]

2 minutes ago, RAvirani said:

The Google Maps Javascript API is $7 per 1000 loads, and you get a $200 per month of free usage. As long as we stay below roughly 28,500 loads per month, we shouldn't have issues using the Google Maps API.  

https://cloud.google.com/maps-platform/pricing/

Yeah, I thought I could get away with that, but it expired after a year.

- Trip

[Flompholph]

Is there a reason that the SQL could not be deleted and re imported similar to how I do the SCP notes DB. I use DB Browser to delete the data. Then I export a csv from google sheets to import back in to the specific table in the database. But I have to admit that I have no real experience with SQL.

If the new google map still use kml it should not be that hard to make a .kml text file with google sheet script. Either way s4gru should be able to use google sheets to edit the data then have a really limited and trusted group to take the sheets and move it to a map easily. 

[chamb]

You also probably need to consider any T-mobile merger and what it may do to the maps.  would you try to place all t-moble sites on the Sprint map? Also, would you eventually wind up with everything being t-mobile in a year or two?

[S4GRU]

3 hours ago, chamb said:

You also probably need to consider any T-mobile merger and what it may do to the maps.  would you try to place all t-moble sites on the Sprint map? Also, would you eventually wind up with everything being t-mobile in a year or two?

Yeah, I would have love to have waited until the merger.  But Google says no.  Not sure what we are going to do about Tmo and 5G.  Have to see what happens.

Robert

[dkyeager]

The other issue is the number of pin types supported.  The merger transition period could put high demands on this type of info given we could have a wide array of the type of actives sites.

[Flompholph]

So I was able to get different colored pins to show up in mapbox. This may help https://docs.mapbox.com/help/tutorials/add-points-pt-2/#style-as-a-symbol-layer

Pretty much have to create a new SVG (image of the pin https://labs.mapbox.com/maki-icons/editor/) then set conditions for each. So you can use the same pin names as fusion but you have to set that "condition" (the fusion pin name) for the svg you made.

Not sure how to pull info for a popup within their studio yet. At the very least we should have a map with different color pins if the fusion death time passes.

[RAvirani]

I've went ahead and yesterday built a rough draft of the application I was talking about. It allows you to edit rows (single site records) with a GUI that then updates a map. You can also use SQL to edit records. The program additionally allows you to manage several maps in one place. Using it feels very similar to using fusiontables. I've PMed a link to @S4GRU and am awaiting feedback. 

I would share a demo here, but the map I'm using for testing has information for ~1000 Sprint sites which I don't think I can post outside the sponsor forum. 

[radem]

2 hours ago, RAvirani said:

I've went ahead and yesterday built a rough draft of the application I was talking about. It allows you to edit rows (single site records) with a GUI that then updates a map. You can also use SQL to edit records. The program additionally allows you to manage several maps in one place. Using it feels very similar to using fusiontables. I've PMed a link to @S4GRU and am awaiting feedback. 

Any chance you could add a bulk upload and bulk download feature?  The bulk upload should give the option to append data or to replace the data.  The bulk download should download the data in CSV format.

The easiest way to get data out of fusion tables is by downloading the fusion table to a comma separated value (CSV) text file.  CSV files can be opened and edited in any common spreadsheet applications to put the data in the format you request.  I would hate to make someone type in many 10's of thousands of sites one at a time.

[RAvirani]

13 minutes ago, radem said:

Any chance you could add a bulk upload and bulk download feature?  The bulk upload should give the option to append data or to replace the data.  The bulk download should download the data in CSV format.

The easiest way to get data out of fusion tables is by downloading the fusion table to a comma separated value (CSV) text file.  CSV files can be opened and edited in any common spreadsheet applications to put the data in the format you request.  I would hate to make someone type in many 10's of thousands of sites one at a time.

Yes - I forgot to mention it has a bulk uploading feature, as well. You can specify whether to replace/update already existing sites or leave them as is (this is because site IDs are unique - they're set up as indexes in the MySQL table). When uploading you can also specify field delimiter, record delimiter, column enclosure and escape characters. 

You can download data in TSV format (tab-delimited). You can also download a SQL script that will input the data into any other SQL database. 

[Flompholph]

So the google API only one person would have access to the script? Then everyone can edit a google sheet and limit the people who edits through google sheet?

Also on the mapbox front I learned mapbox studio has a 5 MB limit for datasets (where you can edit in Studio).  So you have to replace the tileset with a CSV. I put a demo of the Pittsburgh map in the honored section which was a pain to get the map to size correctly.  Also looking at the full NV data through chrome makes my PC hurt a little 65% on a i7 5930K.

[RAvirani]

Here is a rough draft small demo map with a few MMIMO sites in the greater Seattle area:

https://projects.viranifamily.net/s4gru/mysql-maps/?map=mmimo_western_wa

This is what most users will see.

I will be making performance enhancements in the coming weeks, although the map seems to perform well with all of the datasets I've tested thus far - up to 9,000 pins.  

I currently have not had time to make a publicly-available backend for people to fiddle with, but @S4GRU had a chance to take a look at it and can speak to how it works.  

[S4GRU]

2 minutes ago, RAvirani said:

Here is a small demo map with a few 5G sites in the greater Seattle area:

https://projects.viranifamily.net/s4gru/mysql-maps/?map=mmimo_western_wa

This is what most users will see.

I current have not had time to make a publicly available backend for people to fiddle with, but @S4GRU had a chance to take a look at it and can speak to how it works.  

When we get to a finished product, probably the best way to handle it for our Database editors is to keep a Google Doc that we can keep edited.  Then export that to update a SQL Database and then all the markers.  That's probably easier (and safer) than giving everyone access to the SQL database.

That's essentially what we did in the past.  It's just we used Fusion Tables as the database that we edited and then exported it into a Fusion Table static data map.

Robert

[S4GRU]

All:

I've been busy the past two days converting maps to BatchGeo.  Which will we use to host our maps temporarily for a month or two while we work out a better long term solution. 

If you want me to recreate a map in one of your threads, send me the CSV file of the data and the the thread URL and I will create it in BatchGeo.  I'd like to try to keep the site not looking like we were napalmed and burned up all the maps.  I'm happy to create the maps for you.  No matter how old the data.  I like having historical data from years past, especially instead of broken links and missing maps.

BatchGeo is limited to just 25,000 markers.  So maps with more than than will need to be broken down into multiple maps.  There is no extra charge for additional maps.  We just pay a flat $99 per month fee.

THANK YOU EVERYONE who have been helping with the maps.  It is greatly appreciated.

Robert

[Flompholph]

Looks like the dead line has passed and dead maps are showing up. If anyone needs help with mapbox let me know.

[dkyeager]

FYI kml maps created by fusion tables still work with google earth, even the latest version.

[RAvirani]

If anyone manages a spreadsheet for their market and is still looking for/exploring options to display it on a map, PM me. If you can export your database/spreadsheet into a TSV, I've built a tool that produces a map. 

[dkyeager]

On 12/7/2019 at 6:29 PM, RAvirani said:

If anyone manages a spreadsheet for their market and is still looking for/exploring options to display it on a map, PM me. If you can export your database/spreadsheet into a TSV, I've built a tool that produces a map. 

How many different icon choices?