1 package FS::deploy_zone;
4 use base qw( FS::o2m_Common FS::Record );
5 use FS::Record qw( qsearch qsearchs dbh );
11 use HTTP::Request::Common;
13 use Geo::JSON::Polygon;
14 use Geo::JSON::Feature;
16 # update this in 2020, along with the URL for the TIGERweb service
17 our $CENSUS_YEAR = 2010;
19 our $tech_label = FS::part_pkg_fcc_option->technology_labels;
23 FS::deploy_zone - Object methods for deploy_zone records
29 $record = new FS::deploy_zone \%hash;
30 $record = new FS::deploy_zone { 'column' => 'value' };
32 $error = $record->insert;
34 $error = $new_record->replace($old_record);
36 $error = $record->delete;
38 $error = $record->check;
42 An FS::deploy_zone object represents a geographic zone where a certain kind
43 of service is available. Currently we store this information to generate
44 the FCC Form 477 deployment reports, but it may find other uses later.
46 FS::deploy_zone inherits from FS::Record. The following fields are currently
57 Optional text describing the zone.
61 The agent that serves this zone.
65 The census map year for which this zone was last updated. May be null for
66 zones that contain no census blocks (mobile zones, or fixed zones that haven't
67 had their block lists filled in yet).
71 The name under which service is marketed in this zone. If null, will
72 default to the agent name.
76 The way the zone geography is defined: "B" for a list of census blocks
77 (used by the FCC for fixed broadband service), "P" for a polygon (for
78 mobile services). See L<FS::deploy_zone_block> and L<FS::deploy_zone_vertex>.
79 Note that block-type zones are still allowed to have a vertex list, for
80 use by the map editor.
84 The FCC technology code for the type of service available.
88 For mobile service zones, the FCC code for the RF band.
92 For broadband, the advertised upstream bandwidth in the zone. If multiple
93 speed tiers are advertised, use the highest.
97 For broadband, the advertised downstream bandwidth in the zone.
101 For broadband, the contractually guaranteed upstream bandwidth, if that type
106 For broadband, the contractually guaranteed downstream bandwidth, if that
107 type of service is sold.
111 'Y' if this service is sold for consumer/household use.
115 'Y' if this service is sold to business or institutional use. Not mutually
116 exclusive with is_consumer.
120 'Y' if this service includes broadband Internet.
124 'Y' if this service includes voice communication.
128 The date this zone became active.
132 The date this zone became inactive, if any.
142 Creates a new zone. To add the zone to the database, see L<"insert">.
146 # the new method can be inherited from FS::Record, if a table method is defined
148 sub table { 'deploy_zone'; }
150 =item insert ELEMENTS
152 Adds this record to the database. If there is an error, returns the error,
153 otherwise returns false.
157 # the insert method can be inherited from FS::Record
161 Delete this record from the database.
166 my $oldAutoCommit = $FS::UID::AutoCommit;
167 local $FS::UID::AutoCommit = 0;
168 # clean up linked records
171 foreach (qw(deploy_zone_block deploy_zone_vertex)) {
172 $error ||= $self->process_o2m(
174 'num_col' => 'zonenum',
175 'fields' => 'zonenum',
179 $error ||= $self->SUPER::delete(@_);
182 dbh->rollback if $oldAutoCommit;
188 =item replace OLD_RECORD
190 Replaces the OLD_RECORD with this one in the database. If there is an error,
191 returns the error, otherwise returns false.
197 my $old = shift || $self->replace_old;
199 $self->expire_date(time)
200 if $self->disabled eq 'Y' && ! $old->disabled && ! $self->expire_date;
202 $self->SUPER::replace($old, @_);
206 Checks all fields to make sure this is a valid zone record. If there is
207 an error, returns the error, otherwise returns false. Called by the insert
216 $self->ut_numbern('zonenum')
217 || $self->ut_text('description')
218 || $self->ut_number('agentnum')
219 || $self->ut_numbern('censusyear')
220 || $self->ut_foreign_key('agentnum', 'agent', 'agentnum')
221 || $self->ut_textn('dbaname')
222 || $self->ut_enum('zonetype', [ 'B', 'P' ])
223 || $self->ut_number('technology')
224 || $self->ut_numbern('spectrum')
225 || $self->ut_decimaln('adv_speed_up', 3)
226 || $self->ut_decimaln('adv_speed_down', 3)
227 || $self->ut_decimaln('cir_speed_up', 3)
228 || $self->ut_decimaln('cir_speed_down', 3)
229 || $self->ut_flag('is_consumer')
230 || $self->ut_flag('is_business')
231 || $self->ut_flag('is_broadband')
232 || $self->ut_flag('is_voice')
233 || $self->ut_numbern('active_date')
234 || $self->ut_numbern('expire_date')
236 return $error if $error;
238 foreach(qw(adv_speed_down adv_speed_up cir_speed_down cir_speed_up)) {
239 if ($self->get('is_broadband')) {
240 if (!$self->get($_)) {
247 if (!$self->get('active_date')) {
248 $self->set('active_date', time);
254 =item deploy_zone_block
256 Returns the census block records in this zone, in order by census block
257 number. Only appropriate to block-type zones.
259 =item deploy_zone_vertex
261 Returns the vertex records for this zone, in order by sequence number.
265 sub deploy_zone_block {
268 table => 'deploy_zone_block',
269 hashref => { zonenum => $self->zonenum },
270 order_by => ' ORDER BY censusblock',
274 sub deploy_zone_vertex {
277 table => 'deploy_zone_vertex',
278 hashref => { zonenum => $self->zonenum },
279 order_by => ' ORDER BY vertexnum',
283 =item shapefile_add SHAPEFILE
285 Adds this deployment zone to the supplied Geo::Shapelib shapefile.
290 my( $self, $shapefile ) = @_;
292 my @coordinates = map { [ $_->longitude, $_->latitude, 0, 0 ] }
293 $self->deploy_zone_vertex;
294 push @coordinates, $coordinates[0];
296 push @{$shapefile->{Shapes}}, { 'Vertices' => \@coordinates };
297 push @{$shapefile->{ShapeRecords}}, [ $tech_label->{$self->technology},
298 $self->adv_speed_down,
306 Returns the vertex list for this zone, as a JSON string of
308 [ [ latitude0, longitude0 ], [ latitude1, longitude1 ] ... ]
314 my @vertices = map { [ $_->latitude, $_->longitude ] } $self->deploy_zone_vertex;
315 encode_json(\@vertices);
318 =item geo_json_feature
320 Returns this zone as a Geo::JSON::Feature object
324 sub geo_json_feature {
327 my @coordinates = map { [ $_->longitude, $_->latitude ] }
328 $self->deploy_zone_vertex;
329 push @coordinates, $coordinates[0];
331 Geo::JSON::Feature->new({
332 geometry => Geo::JSON::Polygon->new({ coordinates => [ \@coordinates ] }),
333 properties => { 'Technology' => $tech_label->{$self->technology},
334 'Down' => $self->adv_speed_down,
335 'Up' => $self->adv_speed_up,
342 Adds this deployment zone to the supplied Geo::GoogleEarth::Pluggable object.
347 my( $self, $kml ) = @_;
349 my $name = $self->description. ' ('. $self->adv_speed_down. '/'.
350 $self->adv_speed_up. ')';
352 $kml->Polygon( 'name' => $name,
353 'coordinates' => [ [ #outerBoundary
354 map { [ $_->longitude, $_->latitude, 0 ] }
355 $self->deploy_zone_vertex
367 =item process_batch_import JOB, PARAMS
371 sub process_batch_import {
373 use FS::deploy_zone_block;
374 use FS::deploy_zone_vertex;
379 $param = thaw(decode_base64($param));
382 # even if creating a new zone, the deploy_zone object should already
383 # be inserted by this point
384 my $zonenum = $param->{zonenum}
385 or die "zonenum required";
386 my $zone = FS::deploy_zone->by_key($zonenum)
387 or die "deploy_zone #$zonenum not found";
389 if ( $zone->zonetype eq 'B' ) {
390 $opt = { 'table' => 'deploy_zone_block',
391 'params' => [ 'zonenum', 'censusyear' ],
392 'formats' => { 'plain' => [ 'censusblock' ] },
395 $job->update_statustext('1,Inserting census blocks');
396 } elsif ( $zone->zonetype eq 'P' ) {
397 $opt = { 'table' => 'deploy_zone_vertex',
398 'params' => [ 'zonenum' ],
399 'formats' => { 'plain' => [ 'latitude', 'longitude' ] },
403 die "don't know how to import to zonetype ".$zone->zonetype;
406 FS::Record::process_batch_import( $job, $opt, $param );
410 =item process_block_lookup JOB, ZONENUM
412 Look up all the census blocks in the zone's footprint, and insert them.
413 This will replace any existing block list.
417 sub process_block_lookup {
421 $param = thaw(decode_base64($param));
423 my $zonenum = $param->{zonenum};
424 my $zone = FS::deploy_zone->by_key($zonenum)
425 or die "zone $zonenum not found\n";
427 # wipe the existing list of blocks
428 my $error = $zone->process_o2m(
429 'table' => 'deploy_zone_block',
430 'num_col' => 'zonenum',
431 'fields' => 'zonenum',
434 die $error if $error;
436 $job->update_statustext('0,querying census database') if $job;
438 # negotiate the rugged jungle trails of the ArcGIS REST protocol:
439 # 1. unlike most places, longitude first.
440 my @zone_vertices = map { [ $_->longitude, $_->latitude ] }
441 $zone->deploy_zone_vertex;
443 return if scalar(@zone_vertices) < 3; # then don't bother
445 # 2. package this as "rings", inside a JSON geometry object
446 # 3. announce loudly and frequently that we are using spatial reference
447 # 4326, "true GPS coordinates"
448 my $geometry = encode_json({
449 'rings' => [ \@zone_vertices ],
455 geometry => $geometry,
456 geometryType => 'esriGeometryPolygon', # as opposed to a bounding box
459 spatialRel => 'esriSpatialRelIntersects', # the test to perform
460 outFields => 'OID,GEOID',
461 returnGeometry => 'false',
462 orderByFields => 'OID',
464 my $url = 'https://tigerweb.geo.census.gov/arcgis/rest/services/TIGERweb/Tracts_Blocks/MapServer/12/query';
465 my $ua = LWP::UserAgent->new;
467 # first find out how many of these we're dealing with
468 my $response = $ua->request(
469 POST $url, Content => [
471 returnCountOnly => 1,
474 die $response->status_line unless $response->is_success;
475 my $data = decode_json($response->content);
476 # their error messages are mostly useless, but don't just blindly continue
477 die $data->{error}{message} if $data->{error};
479 my $count = $data->{count};
482 #warn "Census block lookup: $count\n";
484 # we have to do our own pagination on this, because the census bureau
485 # doesn't support resultOffset (maybe they don't have ArcGIS 10.3 yet).
486 # that's why we're ordering by OID, it's globally unique
490 $response = $ua->request(
491 POST $url, Content => [
493 where => "OID>$last_oid",
496 die $response->status_line unless $response->is_success;
497 $data = decode_json($response->content);
498 die $data->{error}{message} if $data->{error};
499 last unless scalar @{$data->{features}}; #Nothing to insert
501 foreach my $feature (@{ $data->{features} }) {
502 my $geoid = $feature->{attributes}{GEOID}; # the prize
503 my $block = FS::deploy_zone_block->new({
505 censusblock => $geoid
507 $error = $block->insert;
508 die "$error (inserting census block $geoid)" if $error;
511 if ($job and $inserted % 100 == 0) {
512 my $percent = sprintf('%.0f', $inserted / $count * 100);
513 $job->update_statustext("$percent,creating block records");
517 #warn "Inserted $inserted records\n";
518 $last_oid = $data->{features}[-1]{attributes}{OID};
519 $done = 1 unless $data->{exceededTransferLimit};
522 $zone->set('censusyear', $CENSUS_YEAR);
523 $error = $zone->replace;
524 warn "$error (updating zone census year)" if $error; # whatever, continue