Talk:Custom Highway Shields

From OpenStreetMap Wiki
Jump to: navigation, search

Description on right?

Any objection to having the description on the right? Not only would that make the tables slightly easier to parse, resizing the page wouldn't make the columns jump, and it could make editing the tables easier (potentially have each table row on one line with the description on the line after). And, most important of all, it would decrease the intra-table whitespace. --goldfndr 10:43, 8 January 2010 (UTC)

Sounds good to me. --Hawke 16:46, 8 January 2010 (UTC)
Done. --goldfndr 22:57, 10 January 2010 (UTC)

Additional files?

I tried uploading a few more SVGs from Wikimedia, but they errored out. Is anyone interested in taking a stab at the rest of the US Road Shields? --DiverCTH 05:10, 8 February 2010 (UTC)

Upload? There's no need to upload, and uploading of duplicates is unopposed for cleanup. --goldfndr 10:25, 8 February 2010 (UTC)

Parser

here is the start of a parser, from john smith edited by me.

Code

<?
        function download_wiki_image($wiki_html)
        {
                list($url, $ignore) = explode('|', $wiki_html, 2);

                $wikiurl = "http://wiki.openstreetmap.org/api.php?action=query&prop=imageinfo&iiprop=url&format=xml&titles=";

                $wikiurl .= substr($url, 2);                      
                $xml = file_get_contents($wikiurl);

                list($stuff, $xml) = explode("<ii url=", $xml, 2);
                list($stuff, $url, $stuff) = explode("\"", $xml, 3);

//              $url contains the url of the svg image...
                return $url;
        }

       function build_tree($objects, $object, $country)
       {
               if(count($object) <= 0)
                       return $objects;

               $state = $object['state'];
               unset($object['state']);

               $objects[$country][$state][] = $object;

               return $objects;
       }



# build a pgsql update query to 
function create_database()
{
$table_name="admin_roads";
    print "alter table $table_name add icon varchar(300) NULL;\n";
    print "create index $table_name_icon on $table_name(icon);\n";
}



function update_image_tag($object)
{

$table_name="admin_roads";

    print  "update  " . $table_name  . " set icon='" . $object['Image'] . "' where (icon is null) " ;

    if ($object['Ref'])
    {
	print " and (substring(ref from '" . $object['Ref'] . "') IS NOT NULL)"	    ;
    }

    if ($object['Network'])
    {
	 print   " and (substring(network from '" . $object['Network'] . "') IS NOT NULL) "	    ;
    }
    print ";\n";
    
}


       function load_lookup_table()
       {
               $objects = array();
               $url = 'http://wiki.openstreetmap.org/index.php?title=Custom_Highway_Shields&raw=1&action=raw';

               $data = str_replace('|}', '|-', file_get_contents($url));

#	       print "DATA :$data  \n";

               $lines = explode("\n", $data);

#	       print "LINES :$lines  \n";
#	       print_r($lines);

               $start = false;
               $country = '';
               $ln = 0;

               $object = array();

               foreach($lines as $line)
               {
#		   print "LINE :$line  \n";

                       $line = trim($line);

# {|class="wikitable"
# !colspan="7" | Country
# |-
# ! State
# ! LGA
# ! Network
# ! Ref
# ! Shield
# ! SVG Image
# ! Description/Notes

               if($start == false && $line != '{|class="wikitable"')
	       {
#		   print "SKIP LINE :$line  \n";
                   continue;
	       }


                       $start = true;

# start of country !colspan="7" | Countryname
                       if(substr($line, 0, 14) == '!colspan="7" |')
                       {
                               $objects = build_tree($objects, $object, $country);
                               $object = array();
                               $ln = 0;

                               $country = trim(substr($line, 14,-1));
#			       print "Country " . $country . "\n";
                               continue;
                       }
               if(substr($line,0,1) == '!')
	       {
#		   print "SKIP LINE :$line  \n"; 
#column headings, we could save them.
                   continue;
	       }


		   # no data, or 
                       if($line == '' || $country == '' || substr($line, 0, 1) == '{')
		       {		       
#			       print "step1 $line \n";
                               continue;
		       }


# |-
# | .*
# | .*
# | NH
# | .*
# | au_nh_shield
# | [[Image:Australian_National_Highway.svg|left|50px]]
# | National Highway
# |-

                       if($line == '|-') # start of row
                       {
#			       print "step2 $line\n";
                               $objects = build_tree($objects, $object, $country);
                               $object = array();
                               $ln = 0;

                               continue;
                       }

                       $ln++;

                       $bits = explode('|', $line);
                       $bit = trim($bits[count($bits)-1]);


# ! State
# ! LGA
# ! Network
# ! Ref
# ! Shield
# ! SVG Image
# ! Description/Notes

                       if($ln == 1)
                               $object['State'] = html_entity_decode($bit);
                       if($ln == 2)
                               $object['LGA'] = html_entity_decode($bit);
                       if($ln == 3)
                               $object['Network'] = html_entity_decode($bit);
                       if($ln == 4)
                               $object['Ref'] = html_entity_decode($bit);
                       if($ln == 5)
			   $object['Image'] = html_entity_decode(trim(substr($line, 1)));
                       if($ln == 6)
                               $object['Description'] = html_entity_decode($bit);

#		       print_r ($object);
		       if ($object['Image'])
		       {
			   update_image_tag($object);
		       }
		   

               }

               $objects = build_tree($objects, $object, $country);

#	       print_r ($objects);
               return $objects;
       }

create_database();
load_lookup_table();

example output

alter table admin_roads add icon varchar(300) NULL;
create index  on admin_roads(icon);
update  admin_roads set icon='[[Image:Australian_National_Highway.svg|left|50px]]' where (icon is null)  and (substring(ref from '^..?$') IS NOT NULL) and (substring(network from 'NH') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Australian_National_Highway.svg|left|50px]]' where (icon is null)  and (substring(ref from '^..?$') IS NOT NULL) and (substring(network from 'NH') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Australian_Alphanumeric_National_Route.svg|left|50px]]' where (icon is null)  and (substring(ref from '^....?$') IS NOT NULL) and (substring(network from 'NH') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Australian_Alphanumeric_National_Route.svg|left|50px]]' where (icon is null)  and (substring(ref from '^....?$') IS NOT NULL) and (substring(network from 'NH') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Australian_Highway.svg|left|50px]]' where (icon is null)  and (substring(ref from '.*') IS NOT NULL) and (substring(network from 'NR') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Australian_Highway.svg|left|50px]]' where (icon is null)  and (substring(ref from '.*') IS NOT NULL) and (substring(network from 'NR') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Australian_metroad_template.svg|left|50px]]' where (icon is null)  and (substring(ref from '.*') IS NOT NULL) and (substring(network from 'MR') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Australian_metroad_template.svg|left|50px]]' where (icon is null)  and (substring(ref from '.*') IS NOT NULL) and (substring(network from 'MR') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield australia state route blank.svg|left|50px]]' where (icon is null)  and (substring(ref from '.*') IS NOT NULL) and (substring(network from 'S') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield australia state route blank.svg|left|50px]]' where (icon is null)  and (substring(ref from '.*') IS NOT NULL) and (substring(network from 'S') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Australian_Alphanumeric_State_Route.svg|left|50px]]' where (icon is null)  and (substring(ref from '^[M|A|B|C|D]\d') IS NOT NULL) and (substring(network from '.*') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Australian_Alphanumeric_State_Route.svg|left|50px]]' where (icon is null)  and (substring(ref from '^[M|A|B|C|D]\d') IS NOT NULL) and (substring(network from '.*') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Australian_Alphanumeric_State_Route_ALT.svg|left|50px]]' where (icon is null)  and (substring(ref from '^[M|A|B|C|D]\d') IS NOT NULL) and (substring(network from 'alt') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Australian_Alphanumeric_State_Route_ALT.svg|left|50px]]' where (icon is null)  and (substring(ref from '^[M|A|B|C|D]\d') IS NOT NULL) and (substring(network from 'alt') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Shield australia tourist blank.svg|left|50px]]' where (icon is null)  and (substring(ref from '.*') IS NOT NULL) and (substring(network from 'T') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Shield australia tourist blank.svg|left|50px]]' where (icon is null)  and (substring(ref from '.*') IS NOT NULL) and (substring(network from 'T') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Shield australia tourist brisbane.svg|left|50px]]' where (icon is null)  and (substring(ref from 'brisbane_valley') IS NOT NULL) and (substring(network from 'T') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Shield australia tourist brisbane.svg|left|50px]]' where (icon is null)  and (substring(ref from 'brisbane_valley') IS NOT NULL) and (substring(network from 'T') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Shield australia tourist bunya.svg|left|50px]]' where (icon is null)  and (substring(ref from 'bunya') IS NOT NULL) and (substring(network from 'T') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Shield australia tourist bunya.svg|left|50px]]' where (icon is null)  and (substring(ref from 'bunya') IS NOT NULL) and (substring(network from 'T') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Shield australia tourist wheel.svg|left|50px]]' where (icon is null)  and (substring(ref from 'cobbco') IS NOT NULL) and (substring(network from 'T') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Shield australia tourist wheel.svg|left|50px]]' where (icon is null)  and (substring(ref from 'cobbco') IS NOT NULL) and (substring(network from 'T') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Shield australia tourist dugong.svg|left|50px]]' where (icon is null)  and (substring(ref from 'dugong') IS NOT NULL) and (substring(network from 'T') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Shield australia tourist dugong.svg|left|50px]]' where (icon is null)  and (substring(ref from 'dugong') IS NOT NULL) and (substring(network from 'T') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Shield australia tourist muttaburrasaurus.svg|left|50px]]' where (icon is null)  and (substring(ref from 'muttaburrasaurus') IS NOT NULL) and (substring(network from 'T') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Shield australia tourist muttaburrasaurus.svg|left|50px]]' where (icon is null)  and (substring(ref from 'muttaburrasaurus') IS NOT NULL) and (substring(network from 'T') IS NOT NULL) ;
update  admin_roads set icon='[[Image:US_DOT_FHWA_MUTCD_SHS_2004_3-1_M1-1_600x600mm_0.svg|50px]]' where (icon is null)  and (substring(ref from '\d{1,2}') IS NOT NULL) and (substring(network from 'I') IS NOT NULL) ;
update  admin_roads set icon='[[Image:US_DOT_FHWA_MUTCD_SHS_2004_3-1_M1-1_600x600mm_0.svg|50px]]' where (icon is null)  and (substring(ref from '\d{1,2}') IS NOT NULL) and (substring(network from 'I') IS NOT NULL) ;
update  admin_roads set icon='[[Image:US_DOT_FHWA_MUTCD_SHS_2004_3-1_M1-1_750x600mm_000.svg|50px]]' where (icon is null)  and (substring(ref from '(\d{3}|\d{2}[EW])') IS NOT NULL) and (substring(network from 'I') IS NOT NULL) ;
update  admin_roads set icon='[[Image:US_DOT_FHWA_MUTCD_SHS_2004_3-1_M1-1_750x600mm_000.svg|50px]]' where (icon is null)  and (substring(ref from '(\d{3}|\d{2}[EW])') IS NOT NULL) and (substring(network from 'I') IS NOT NULL) ;
update  admin_roads set icon='[[Image:US_blank.svg|50px]]' where (icon is null)  and (substring(ref from '\d{1,2}') IS NOT NULL) and (substring(network from 'US') IS NOT NULL) ;
update  admin_roads set icon='[[Image:US_blank.svg|50px]]' where (icon is null)  and (substring(ref from '\d{1,2}') IS NOT NULL) and (substring(network from 'US') IS NOT NULL) ;
update  admin_roads set icon='[[Image:US_blank_wide.svg|50px]]' where (icon is null)  and (substring(ref from '\d{3}') IS NOT NULL) and (substring(network from 'US') IS NOT NULL) ;
update  admin_roads set icon='[[Image:US_blank_wide.svg|50px]]' where (icon is null)  and (substring(ref from '\d{3}') IS NOT NULL) and (substring(network from 'US') IS NOT NULL) ;
update  admin_roads set icon='[[Image:US_blank_(CA).svg|50px]]' where (icon is null)  and (substring(ref from '.*') IS NOT NULL) and (substring(network from 'US') IS NOT NULL) ;
update  admin_roads set icon='[[Image:US_blank_(CA).svg|50px]]' where (icon is null)  and (substring(ref from '.*') IS NOT NULL) and (substring(network from 'US') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Shield state generic blank.svg|50px]]' where (icon is null)  and (substring(ref from '\d{2}') IS NOT NULL) and (substring(network from '(US:DE|US:IA|US:KY)') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Shield state generic blank.svg|50px]]' where (icon is null)  and (substring(ref from '\d{2}') IS NOT NULL) and (substring(network from '(US:DE|US:IA|US:KY)') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Shield state generic blank.svg|50px]]' where (icon is null)  and (substring(ref from '\d{3}') IS NOT NULL) and (substring(network from 'US:IA') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Shield state generic blank.svg|50px]]' where (icon is null)  and (substring(ref from '\d{3}') IS NOT NULL) and (substring(network from 'US:IA') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Shield state generic blank wide.svg|50px]]' where (icon is null)  and (substring(ref from '\d{3}') IS NOT NULL) and (substring(network from '(US:DE|US:KY)') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Shield state generic blank wide.svg|50px]]' where (icon is null)  and (substring(ref from '\d{3}') IS NOT NULL) and (substring(network from '(US:DE|US:KY)') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Shield state alabama blank.svg|50px]]' where (icon is null)  and (substring(ref from '\d{2}') IS NOT NULL) and (substring(network from 'US:AL') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Shield state alabama blank.svg|50px]]' where (icon is null)  and (substring(ref from '\d{2}') IS NOT NULL) and (substring(network from 'US:AL') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Alabama 000 Template.svg|50px]]' where (icon is null)  and (substring(ref from '\d{3}') IS NOT NULL) and (substring(network from 'US:AL') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Alabama 000 Template.svg|50px]]' where (icon is null)  and (substring(ref from '\d{3}') IS NOT NULL) and (substring(network from 'US:AL') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Shield state alaska blank.svg|50px]]' where (icon is null)  and (substring(ref from '.*') IS NOT NULL) and (substring(network from 'US:AK') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Shield state alaska blank.svg|50px]]' where (icon is null)  and (substring(ref from '.*') IS NOT NULL) and (substring(network from 'US:AK') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield state alaska business template.svg|50px]]' where (icon is null)  and (substring(ref from '.*') IS NOT NULL) and (substring(network from 'US:AK:BUS') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield state alaska business template.svg|50px]]' where (icon is null)  and (substring(ref from '.*') IS NOT NULL) and (substring(network from 'US:AK:BUS') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Shield state arizona template.svg|50px]]' where (icon is null)  and (substring(ref from '.{2}') IS NOT NULL) and (substring(network from 'US:AZ') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Shield state arizona template.svg|50px]]' where (icon is null)  and (substring(ref from '.{2}') IS NOT NULL) and (substring(network from 'US:AZ') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield state arizona template wide.svg|50px]]' where (icon is null)  and (substring(ref from '.{3}') IS NOT NULL) and (substring(network from 'US:AZ') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield state arizona template wide.svg|50px]]' where (icon is null)  and (substring(ref from '.{3}') IS NOT NULL) and (substring(network from 'US:AZ') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Shield state arkansas blank.svg|50px]]' where (icon is null)  and (substring(ref from '.*') IS NOT NULL) and (substring(network from 'US:AR') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Shield state arkansas blank.svg|50px]]' where (icon is null)  and (substring(ref from '.*') IS NOT NULL) and (substring(network from 'US:AR') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield state california blank.svg|50px]]' where (icon is null)  and (substring(ref from '\d{1,2}') IS NOT NULL) and (substring(network from 'US:CA') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield state california blank.svg|50px]]' where (icon is null)  and (substring(ref from '\d{1,2}') IS NOT NULL) and (substring(network from 'US:CA') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield state california blank wide.svg|50px]]' where (icon is null)  and (substring(ref from '\d{3}') IS NOT NULL) and (substring(network from 'US:CA') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield state california blank wide.svg|50px]]' where (icon is null)  and (substring(ref from '\d{3}') IS NOT NULL) and (substring(network from 'US:CA') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Shield state colorado blank.svg|50px]]' where (icon is null)  and (substring(ref from '.*') IS NOT NULL) and (substring(network from 'US:CO') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Shield state colorado blank.svg|50px]]' where (icon is null)  and (substring(ref from '.*') IS NOT NULL) and (substring(network from 'US:CO') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Shield state connecticut blank.svg|50px]]' where (icon is null)  and (substring(ref from '.*') IS NOT NULL) and (substring(network from 'US:CT') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Shield state connecticut blank.svg|50px]]' where (icon is null)  and (substring(ref from '.*') IS NOT NULL) and (substring(network from 'US:CT') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Shield state dc blank.svg|50px]]' where (icon is null)  and (substring(ref from '.*') IS NOT NULL) and (substring(network from 'US:DC') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Shield state dc blank.svg|50px]]' where (icon is null)  and (substring(ref from '.*') IS NOT NULL) and (substring(network from 'US:DC') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield state florida blank.svg|50px]]' where (icon is null)  and (substring(ref from '.{1,2}') IS NOT NULL) and (substring(network from 'US:FL') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield state florida blank.svg|50px]]' where (icon is null)  and (substring(ref from '.{1,2}') IS NOT NULL) and (substring(network from 'US:FL') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield state florida blank wide.svg|50px]]' where (icon is null)  and (substring(ref from '.{3,4}') IS NOT NULL) and (substring(network from 'US:FL') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield state florida blank wide.svg|50px]]' where (icon is null)  and (substring(ref from '.{3,4}') IS NOT NULL) and (substring(network from 'US:FL') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield state georgia blank.svg|50px]]' where (icon is null)  and (substring(ref from '.*') IS NOT NULL) and (substring(network from 'US:GA') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield state georgia blank.svg|50px]]' where (icon is null)  and (substring(ref from '.*') IS NOT NULL) and (substring(network from 'US:GA') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield state hawaii blank.svg|50px]]' where (icon is null)  and (substring(ref from '.*') IS NOT NULL) and (substring(network from 'US:HI') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield state hawaii blank.svg|50px]]' where (icon is null)  and (substring(ref from '.*') IS NOT NULL) and (substring(network from 'US:HI') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield state idaho blank.svg|50px]]' where (icon is null)  and (substring(ref from '.*') IS NOT NULL) and (substring(network from 'US:ID') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield state idaho blank.svg|50px]]' where (icon is null)  and (substring(ref from '.*') IS NOT NULL) and (substring(network from 'US:ID') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield state illinois blank.svg|50px]]' where (icon is null)  and (substring(ref from '.{1,2}') IS NOT NULL) and (substring(network from 'US:IL') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield state illinois blank.svg|50px]]' where (icon is null)  and (substring(ref from '.{1,2}') IS NOT NULL) and (substring(network from 'US:IL') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield state illinois blank wide.svg|50px]]' where (icon is null)  and (substring(ref from '.{3}') IS NOT NULL) and (substring(network from 'US:IL') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield state illinois blank wide.svg|50px]]' where (icon is null)  and (substring(ref from '.{3}') IS NOT NULL) and (substring(network from 'US:IL') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield state indiana blank.svg|50px]]' where (icon is null)  and (substring(ref from '.{1,2}') IS NOT NULL) and (substring(network from 'US:IN') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield state indiana blank.svg|50px]]' where (icon is null)  and (substring(ref from '.{1,2}') IS NOT NULL) and (substring(network from 'US:IN') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield state indiana blank wide.svg|50px]]' where (icon is null)  and (substring(ref from '.{3}') IS NOT NULL) and (substring(network from 'US:IN') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield state indiana blank wide.svg|50px]]' where (icon is null)  and (substring(ref from '.{3}') IS NOT NULL) and (substring(network from 'US:IN') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield state kansas blank.svg|50px]]' where (icon is null)  and (substring(ref from '.{1,2}') IS NOT NULL) and (substring(network from 'US:KS') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield state kansas blank.svg|50px]]' where (icon is null)  and (substring(ref from '.{1,2}') IS NOT NULL) and (substring(network from 'US:KS') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield state kansas blank wide.svg|50px]]' where (icon is null)  and (substring(ref from '.{3}') IS NOT NULL) and (substring(network from 'US:KS') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield state kansas blank wide.svg|50px]]' where (icon is null)  and (substring(ref from '.{3}') IS NOT NULL) and (substring(network from 'US:KS') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield state louisiana blank.svg|50px]]' where (icon is null)  and (substring(ref from '.*') IS NOT NULL) and (substring(network from 'US:LA') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield state louisiana blank.svg|50px]]' where (icon is null)  and (substring(ref from '.*') IS NOT NULL) and (substring(network from 'US:LA') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield state maryland template.svg|50px]]' where (icon is null)  and (substring(ref from '.{1,2}') IS NOT NULL) and (substring(network from 'US:MD') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield state maryland template.svg|50px]]' where (icon is null)  and (substring(ref from '.{1,2}') IS NOT NULL) and (substring(network from 'US:MD') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield state maryland template wide.svg|50px]]' where (icon is null)  and (substring(ref from '.{3}') IS NOT NULL) and (substring(network from 'US:MD') IS NOT NULL) ;
update  admin_roads set icon='[[Image:shield state maryland template wide.svg|50px]]' where (icon is null)  and (substring(ref from '.{3}') IS NOT NULL) and (substring(network from 'US:MD') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Shield state pennsylvania blank.svg|50px]]' where (icon is null)  and (substring(ref from '.{1,2}') IS NOT NULL) and (substring(network from 'US:PA') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Shield state pennsylvania blank.svg|50px]]' where (icon is null)  and (substring(ref from '.{1,2}') IS NOT NULL) and (substring(network from 'US:PA') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Shield state pennsylvania blank wide.svg|50px]]' where (icon is null)  and (substring(ref from '.{3}') IS NOT NULL) and (substring(network from 'US:PA') IS NOT NULL) ;
update  admin_roads set icon='[[Image:Shield state pennsylvania blank wide.svg|50px]]' where (icon is null)  and (substring(ref from '.{3}') IS NOT NULL) and (substring(network from 'US:PA') IS NOT NULL) ;

Regular Expression to HTML Code

<?
	$str = trim($argv['1']);
	for($i = 0; $i < strlen($str); ++$i)
	{
		if(preg_match('/[a-zA-Z0-9]/', substr($str, $i, 1)) > 0)
		{
			echo substr($str, $i, 1);
		} else {
			echo '&#'.ord(substr($str, $i, 1)).';';
		}
	}

	echo "\n";

Black background

The black background is used on highway signs for visibility, but print and online maps usually omit it. We should try to do the same for the U.S. routes and state highways. It's usually just a matter of deleting <path> tags in the SVG that have the fill CSS property set to black. – Minh Nguyễn (talk, contribs) 23:43, 20 February 2010 (UTC)

I'm not sure how SVG files handle it, but these should be made transparent, unless deleting will achieve the same result. -- Delta foxtrot2 03:08, 21 February 2010 (UTC)
Yes, it would have the same result. Any renderer that supports custom markers would need to support transparency, because the black backgrounds have rounded corners anyways. – Minh Nguyễn (talk, contribs) 08:28, 21 February 2010 (UTC)