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.
190 # the replace method can be inherited from FS::Record
194 Checks all fields to make sure this is a valid zone record. If there is
195 an error, returns the error, otherwise returns false. Called by the insert
204 $self->ut_numbern('zonenum')
205 || $self->ut_text('description')
206 || $self->ut_number('agentnum')
207 || $self->ut_numbern('censusyear')
208 || $self->ut_foreign_key('agentnum', 'agent', 'agentnum')
209 || $self->ut_textn('dbaname')
210 || $self->ut_enum('zonetype', [ 'B', 'P' ])
211 || $self->ut_number('technology')
212 || $self->ut_numbern('spectrum')
213 || $self->ut_decimaln('adv_speed_up', 3)
214 || $self->ut_decimaln('adv_speed_down', 3)
215 || $self->ut_decimaln('cir_speed_up', 3)
216 || $self->ut_decimaln('cir_speed_down', 3)
217 || $self->ut_flag('is_consumer')
218 || $self->ut_flag('is_business')
219 || $self->ut_flag('is_broadband')
220 || $self->ut_flag('is_voice')
221 || $self->ut_numbern('active_date')
222 || $self->ut_numbern('expire_date')
224 return $error if $error;
226 foreach(qw(adv_speed_down adv_speed_up cir_speed_down cir_speed_up)) {
227 if ($self->get('is_broadband')) {
228 if (!$self->get($_)) {
235 if (!$self->get('active_date')) {
236 $self->set('active_date', time);
242 =item deploy_zone_block
244 Returns the census block records in this zone, in order by census block
245 number. Only appropriate to block-type zones.
247 =item deploy_zone_vertex
249 Returns the vertex records for this zone, in order by sequence number.
253 sub deploy_zone_block {
256 table => 'deploy_zone_block',
257 hashref => { zonenum => $self->zonenum },
258 order_by => ' ORDER BY censusblock',
262 sub deploy_zone_vertex {
265 table => 'deploy_zone_vertex',
266 hashref => { zonenum => $self->zonenum },
267 order_by => ' ORDER BY vertexnum',
273 Returns the vertex list for this zone, as a JSON string of
275 [ [ latitude0, longitude0 ], [ latitude1, longitude1 ] ... ]
281 my @vertices = map { [ $_->latitude, $_->longitude ] } $self->deploy_zone_vertex;
282 encode_json(\@vertices);
289 =item process_batch_import JOB, PARAMS
293 sub process_batch_import {
295 use FS::deploy_zone_block;
296 use FS::deploy_zone_vertex;
301 $param = thaw(decode_base64($param));
304 # even if creating a new zone, the deploy_zone object should already
305 # be inserted by this point
306 my $zonenum = $param->{zonenum}
307 or die "zonenum required";
308 my $zone = FS::deploy_zone->by_key($zonenum)
309 or die "deploy_zone #$zonenum not found";
311 if ( $zone->zonetype eq 'B' ) {
312 $opt = { 'table' => 'deploy_zone_block',
313 'params' => [ 'zonenum', 'censusyear' ],
314 'formats' => { 'plain' => [ 'censusblock' ] },
317 $job->update_statustext('1,Inserting census blocks');
318 } elsif ( $zone->zonetype eq 'P' ) {
319 $opt = { 'table' => 'deploy_zone_vertex',
320 'params' => [ 'zonenum' ],
321 'formats' => { 'plain' => [ 'latitude', 'longitude' ] },
325 die "don't know how to import to zonetype ".$zone->zonetype;
328 FS::Record::process_batch_import( $job, $opt, $param );
332 =item process_block_lookup JOB, ZONENUM
334 Look up all the census blocks in the zone's footprint, and insert them.
335 This will replace any existing block list.
339 sub process_block_lookup {
343 $param = thaw(decode_base64($param));
345 my $zonenum = $param->{zonenum};
346 my $zone = FS::deploy_zone->by_key($zonenum)
347 or die "zone $zonenum not found\n";
349 # wipe the existing list of blocks
350 my $error = $zone->process_o2m(
351 'table' => 'deploy_zone_block',
352 'num_col' => 'zonenum',
353 'fields' => 'zonenum',
356 die $error if $error;
358 $job->update_statustext('0,querying census database') if $job;
360 # negotiate the rugged jungle trails of the ArcGIS REST protocol:
361 # 1. unlike most places, longitude first.
362 my @zone_vertices = map { [ $_->longitude, $_->latitude ] }
363 $zone->deploy_zone_vertex;
365 return if scalar(@zone_vertices) < 3; # then don't bother
367 # 2. package this as "rings", inside a JSON geometry object
368 # 3. announce loudly and frequently that we are using spatial reference
369 # 4326, "true GPS coordinates"
370 my $geometry = encode_json({
371 'rings' => [ \@zone_vertices ],
377 geometry => $geometry,
378 geometryType => 'esriGeometryPolygon', # as opposed to a bounding box
381 spatialRel => 'esriSpatialRelIntersects', # the test to perform
382 outFields => 'OID,GEOID',
383 returnGeometry => 'false',
384 orderByFields => 'OID',
386 my $url = 'https://tigerweb.geo.census.gov/arcgis/rest/services/TIGERweb/Tracts_Blocks/MapServer/12/query';
387 my $ua = LWP::UserAgent->new;
389 # first find out how many of these we're dealing with
390 my $response = $ua->request(
391 POST $url, Content => [
393 returnCountOnly => 1,
396 die $response->status_line unless $response->is_success;
397 my $data = decode_json($response->content);
398 # their error messages are mostly useless, but don't just blindly continue
399 die $data->{error}{message} if $data->{error};
401 my $count = $data->{count};
404 #warn "Census block lookup: $count\n";
406 # we have to do our own pagination on this, because the census bureau
407 # doesn't support resultOffset (maybe they don't have ArcGIS 10.3 yet).
408 # that's why we're ordering by OID, it's globally unique
412 $response = $ua->request(
413 POST $url, Content => [
415 where => "OID>$last_oid",
418 die $response->status_line unless $response->is_success;
419 $data = decode_json($response->content);
420 die $data->{error}{message} if $data->{error};
422 foreach my $feature (@{ $data->{features} }) {
423 my $geoid = $feature->{attributes}{GEOID}; # the prize
424 my $block = FS::deploy_zone_block->new({
426 censusblock => $geoid
428 $error = $block->insert;
429 die "$error (inserting census block $geoid)" if $error;
432 if ($job and $inserted % 100 == 0) {
433 my $percent = sprintf('%.0f', $inserted / $count * 100);
434 $job->update_statustext("$percent,creating block records");
438 #warn "Inserted $inserted records\n";
439 $last_oid = $data->{features}[-1]{attributes}{OID};
440 $done = 1 unless $data->{exceededTransferLimit};
443 $zone->set('censusyear', $CENSUS_YEAR);
444 $error = $zone->replace;
445 warn "$error (updating zone census year)" if $error; # whatever, continue