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 # update this in 2020, along with the URL for the TIGERweb service
14 our $CENSUS_YEAR = 2010;
18 FS::deploy_zone - Object methods for deploy_zone records
24 $record = new FS::deploy_zone \%hash;
25 $record = new FS::deploy_zone { 'column' => 'value' };
27 $error = $record->insert;
29 $error = $new_record->replace($old_record);
31 $error = $record->delete;
33 $error = $record->check;
37 An FS::deploy_zone object represents a geographic zone where a certain kind
38 of service is available. Currently we store this information to generate
39 the FCC Form 477 deployment reports, but it may find other uses later.
41 FS::deploy_zone inherits from FS::Record. The following fields are currently
52 Optional text describing the zone.
56 The agent that serves this zone.
60 The census map year for which this zone was last updated. May be null for
61 zones that contain no census blocks (mobile zones, or fixed zones that haven't
62 had their block lists filled in yet).
66 The name under which service is marketed in this zone. If null, will
67 default to the agent name.
71 The way the zone geography is defined: "B" for a list of census blocks
72 (used by the FCC for fixed broadband service), "P" for a polygon (for
73 mobile services). See L<FS::deploy_zone_block> and L<FS::deploy_zone_vertex>.
74 Note that block-type zones are still allowed to have a vertex list, for
75 use by the map editor.
79 The FCC technology code for the type of service available.
83 For mobile service zones, the FCC code for the RF band.
87 For broadband, the advertised upstream bandwidth in the zone. If multiple
88 speed tiers are advertised, use the highest.
92 For broadband, the advertised downstream bandwidth in the zone.
96 For broadband, the contractually guaranteed upstream bandwidth, if that type
101 For broadband, the contractually guaranteed downstream bandwidth, if that
102 type of service is sold.
106 'Y' if this service is sold for consumer/household use.
110 'Y' if this service is sold to business or institutional use. Not mutually
111 exclusive with is_consumer.
115 'Y' if this service includes broadband Internet.
119 'Y' if this service includes voice communication.
123 The date this zone became active.
127 The date this zone became inactive, if any.
137 Creates a new zone. To add the zone to the database, see L<"insert">.
141 # the new method can be inherited from FS::Record, if a table method is defined
143 sub table { 'deploy_zone'; }
145 =item insert ELEMENTS
147 Adds this record to the database. If there is an error, returns the error,
148 otherwise returns false.
152 # the insert method can be inherited from FS::Record
156 Delete this record from the database.
161 my $oldAutoCommit = $FS::UID::AutoCommit;
162 local $FS::UID::AutoCommit = 0;
163 # clean up linked records
166 foreach (qw(deploy_zone_block deploy_zone_vertex)) {
167 $error ||= $self->process_o2m(
169 'num_col' => 'zonenum',
170 'fields' => 'zonenum',
174 $error ||= $self->SUPER::delete(@_);
177 dbh->rollback if $oldAutoCommit;
183 =item replace OLD_RECORD
185 Replaces the OLD_RECORD with this one in the database. If there is an error,
186 returns the error, otherwise returns false.
192 my $old = shift || $self->replace_old;
194 $self->expire_date(time)
195 if $self->disabled eq 'Y' && ! $old->disabled && ! $self->expire_date;
197 $self->SUPER::replace($old, @_);
201 Checks all fields to make sure this is a valid zone record. If there is
202 an error, returns the error, otherwise returns false. Called by the insert
211 $self->ut_numbern('zonenum')
212 || $self->ut_text('description')
213 || $self->ut_number('agentnum')
214 || $self->ut_numbern('censusyear')
215 || $self->ut_foreign_key('agentnum', 'agent', 'agentnum')
216 || $self->ut_textn('dbaname')
217 || $self->ut_enum('zonetype', [ 'B', 'P' ])
218 || $self->ut_number('technology')
219 || $self->ut_numbern('spectrum')
220 || $self->ut_decimaln('adv_speed_up', 3)
221 || $self->ut_decimaln('adv_speed_down', 3)
222 || $self->ut_decimaln('cir_speed_up', 3)
223 || $self->ut_decimaln('cir_speed_down', 3)
224 || $self->ut_flag('is_consumer')
225 || $self->ut_flag('is_business')
226 || $self->ut_flag('is_broadband')
227 || $self->ut_flag('is_voice')
228 || $self->ut_numbern('active_date')
229 || $self->ut_numbern('expire_date')
231 return $error if $error;
233 foreach(qw(adv_speed_down adv_speed_up cir_speed_down cir_speed_up)) {
234 if ($self->get('is_broadband')) {
235 if (!$self->get($_)) {
242 if (!$self->get('active_date')) {
243 $self->set('active_date', time);
249 =item deploy_zone_block
251 Returns the census block records in this zone, in order by census block
252 number. Only appropriate to block-type zones.
254 =item deploy_zone_vertex
256 Returns the vertex records for this zone, in order by sequence number.
260 sub deploy_zone_block {
263 table => 'deploy_zone_block',
264 hashref => { zonenum => $self->zonenum },
265 order_by => ' ORDER BY censusblock',
269 sub deploy_zone_vertex {
272 table => 'deploy_zone_vertex',
273 hashref => { zonenum => $self->zonenum },
274 order_by => ' ORDER BY vertexnum',
280 Returns the vertex list for this zone, as a JSON string of
282 [ [ latitude0, longitude0 ], [ latitude1, longitude1 ] ... ]
288 my @vertices = map { [ $_->latitude, $_->longitude ] } $self->deploy_zone_vertex;
289 encode_json(\@vertices);
296 =item process_batch_import JOB, PARAMS
300 sub process_batch_import {
302 use FS::deploy_zone_block;
303 use FS::deploy_zone_vertex;
308 $param = thaw(decode_base64($param));
311 # even if creating a new zone, the deploy_zone object should already
312 # be inserted by this point
313 my $zonenum = $param->{zonenum}
314 or die "zonenum required";
315 my $zone = FS::deploy_zone->by_key($zonenum)
316 or die "deploy_zone #$zonenum not found";
318 if ( $zone->zonetype eq 'B' ) {
319 $opt = { 'table' => 'deploy_zone_block',
320 'params' => [ 'zonenum', 'censusyear' ],
321 'formats' => { 'plain' => [ 'censusblock' ] },
324 $job->update_statustext('1,Inserting census blocks');
325 } elsif ( $zone->zonetype eq 'P' ) {
326 $opt = { 'table' => 'deploy_zone_vertex',
327 'params' => [ 'zonenum' ],
328 'formats' => { 'plain' => [ 'latitude', 'longitude' ] },
332 die "don't know how to import to zonetype ".$zone->zonetype;
335 FS::Record::process_batch_import( $job, $opt, $param );
339 =item process_block_lookup JOB, ZONENUM
341 Look up all the census blocks in the zone's footprint, and insert them.
342 This will replace any existing block list.
346 sub process_block_lookup {
350 $param = thaw(decode_base64($param));
352 my $zonenum = $param->{zonenum};
353 my $zone = FS::deploy_zone->by_key($zonenum)
354 or die "zone $zonenum not found\n";
356 # wipe the existing list of blocks
357 my $error = $zone->process_o2m(
358 'table' => 'deploy_zone_block',
359 'num_col' => 'zonenum',
360 'fields' => 'zonenum',
363 die $error if $error;
365 $job->update_statustext('0,querying census database') if $job;
367 # negotiate the rugged jungle trails of the ArcGIS REST protocol:
368 # 1. unlike most places, longitude first.
369 my @zone_vertices = map { [ $_->longitude, $_->latitude ] }
370 $zone->deploy_zone_vertex;
372 return if scalar(@zone_vertices) < 3; # then don't bother
374 # 2. package this as "rings", inside a JSON geometry object
375 # 3. announce loudly and frequently that we are using spatial reference
376 # 4326, "true GPS coordinates"
377 my $geometry = encode_json({
378 'rings' => [ \@zone_vertices ],
384 geometry => $geometry,
385 geometryType => 'esriGeometryPolygon', # as opposed to a bounding box
388 spatialRel => 'esriSpatialRelIntersects', # the test to perform
389 outFields => 'OID,GEOID',
390 returnGeometry => 'false',
391 orderByFields => 'OID',
393 my $url = 'https://tigerweb.geo.census.gov/arcgis/rest/services/TIGERweb/Tracts_Blocks/MapServer/12/query';
394 my $ua = LWP::UserAgent->new;
396 # first find out how many of these we're dealing with
397 my $response = $ua->request(
398 POST $url, Content => [
400 returnCountOnly => 1,
403 die $response->status_line unless $response->is_success;
404 my $data = decode_json($response->content);
405 # their error messages are mostly useless, but don't just blindly continue
406 die $data->{error}{message} if $data->{error};
408 my $count = $data->{count};
411 #warn "Census block lookup: $count\n";
413 # we have to do our own pagination on this, because the census bureau
414 # doesn't support resultOffset (maybe they don't have ArcGIS 10.3 yet).
415 # that's why we're ordering by OID, it's globally unique
419 $response = $ua->request(
420 POST $url, Content => [
422 where => "OID>$last_oid",
425 die $response->status_line unless $response->is_success;
426 $data = decode_json($response->content);
427 die $data->{error}{message} if $data->{error};
428 last unless scalar @{$data->{features}}; #Nothing to insert
430 foreach my $feature (@{ $data->{features} }) {
431 my $geoid = $feature->{attributes}{GEOID}; # the prize
432 my $block = FS::deploy_zone_block->new({
434 censusblock => $geoid
436 $error = $block->insert;
437 die "$error (inserting census block $geoid)" if $error;
440 if ($job and $inserted % 100 == 0) {
441 my $percent = sprintf('%.0f', $inserted / $count * 100);
442 $job->update_statustext("$percent,creating block records");
446 #warn "Inserted $inserted records\n";
447 $last_oid = $data->{features}[-1]{attributes}{OID};
448 $done = 1 unless $data->{exceededTransferLimit};
451 $zone->set('censusyear', $CENSUS_YEAR);
452 $error = $zone->replace;
453 warn "$error (updating zone census year)" if $error; # whatever, continue